Effecient decimal precision and storage
-
Hello-
I'm still working on a financial performance program and have a question regarding the decimal data type and 2 things:
1) Storage waste
2) Additional calculation time
RE 1)
It seems that particular combinations of scale and precision result in a change in overall "Length" storage size.
Examples:
Scale of 5 and Precision of 3 = Length 5
Scale of 8 and Precision of 3 = Length 5
Scale of 10 and Precision of 3 = Length 9
(test this in the SQL Analyzer GUI)
Is there a happy medium or "standard" when creating decimal types???
RE 2)
What actually factors into calculation time?
The scale, precision, or is the overall "Length" most important?
Example:
Since:
Scale of 5 and Precision of 3 = Length 5
AND
Scale of 9 and Precision of 3 also - Length 5
...
Does adding 10,000 of these numbers together take longer for the Scale 9 Precision 3 data?
Conversely
Since:
Scale of 5 and Precision of 3 = Length 5
AND
Scale of 10 and Precision of 3 also - Length 9
...
Does adding 10,000 of these numbers together take longer for the Scale 10 Precision 3 data?
Or, assuming a maximum of 100,000 rows - should I even give a @^$@# what sizes I use ???
Any guru insight is appreciated - B
-
Hi BillyWilly,
quote:
Is there a happy medium or "standard" when creating decimal types???
in most cases there are authoritative standards about how to do financial calculations. Take a look at http://www.isma.org or http://www.fasb.org . In the financial world quite popular are the Performance Presentation Standards created by http://www.aimr.org. These will leave you little room if you wnat to comply with PPS.
quote:
Or, assuming a maximum of 100,000 rows - should I even give a @^$@# what sizes I use ???
I don't give a thought about storage size. Huge harddrives aren't that expensive today. 100,000 rows aren't that much records and can very easily be reached when I do start a stochastic projection of some 200 assets and 10,000 paths. Although it takes a while to finish, I guess nobody expects a response time <1 s.
What kind of performance program are you working on?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks Frank-
I'm working on basic, internally calculated performance for a set of Mutual Funds and their underlying Managers. Problems arise when attempting to run geometric performance on a daily basis over long periods of time. The Scale and Precision of the daily values for Share Count, Additional Shares, Current Shares, and Daily Performance all factor into the equation. I'm using the SUM(LOG()) method of calculating performance.
SELECT
@Result = SUM(LOG((ReturnNum + 1)))
FROM
dbo.peFundData WITH (NOLOCK)
WHERE
id_MutualFund = @id_MutualFund
AND SnapDte BETWEEN
@BeginDate AND @EndDate
SET @Result = EXP(@Result) - 1
I also have an Annualization formula.
But, as you can see, when running for periods of many years, precision of the performance data comes into play.
- B
-
Hi BillyWilly,
quote:
I'm working on basic, internally calculated performance for a set of Mutual Funds and their underlying Managers. Problems arise when attempting to run geometric performance on a daily basis over long periods of time. The Scale and Precision of the daily values for Share Count, Additional Shares, Current Shares, and Daily Performance all factor into the equation. I'm using the SUM(LOG()) method of calculating performance.
yes, this can be a great issue, especially for time range from 5 years or more. As you are doing this for an basic internally used performance app, don't you think a scale of more than 5 or 6 is an overkill. If I remember correctly, banks mostly use a scale of not more than 6.
Are you one of that poor people that needs to develop a performance calculator, so that the fund manager can proudly present his performance and tracking error?
Please take the following NOT too seriously!!
Most people I met in the financial area are not interested in having accurate but rather plausible (in their sense) results
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Heh-
You've got me pegged !
It seems that using a Beginning versus ending NAV is satisfactory but we'll be using every days data to find the geometric mean using the SUM(LOG()) method.
I think 6 precision will be good enough as well.
- B
-
Hi BillyWilly,
quote:
Heh-You've got me pegged !
It seems that using a Beginning versus ending NAV is satisfactory but we'll be using every days data to find the geometric mean using the SUM(LOG()) method.
if you're using ending NAV for each trading day in the last say five years and compares the results to those you get when you pick one observation each week, I'll bet you'll find the result differ not that significant. And if you have a timerange of at least +five years, one observation per month should also be a quite satisfying approximation. Have you done some testings on this?
When I do performance calculations or benchmarking AND the timerange is at least 1 year, I use weekly data (eg +52 observations in the sample). Saves a lot of runtime without losing significant accuracy.
Hey, are you doing calculations on Sharpe and Treynor?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Sharpe and Treynor = huh?
I can only code the specs I'm given and one of the senior guys here has me using not the NAV, but the daily performance percentage each day. We use the NAV, a Dividend factor, and Share count to compute a daily performance percentage number. Then I run that daily percentage number across whatever time period needed. For periods longer than 1 year, I use an annualization formula.
Still trying to decide upon and utilize User-Defined data types for all this.
BTW - SQL server complaint ahead...
Why can't I use UDF's in certain areas including UDTs ??? And I don't use "Rules" either since Microsoft has deprecated thier usage. Until a new version - I may stick with "on the spot" data type declarations until UDT's are done properly and rules can be enforced.
Here's the annulaization UDF:
CREATE FUNCTION peGetAnnualizedPerformance
(
@CumPerf decimal(7,4),
@PeriodBase decimal(15,4),
@PeriodCount decimal(15,4)
)
RETURNS decimal(7,4)
WITH SCHEMABINDING
AS
/*
Calculate Annualized Performance
Input periods come in as Ints but need to be precision decimals for the division to work properly
*/
BEGIN
DECLARE @Result decimal(15,4)
IF @PeriodCount = 0
SET @Result = 0 /* Account for Divide By Zero Errors */
ELSE
SET @Result = (
POWER((1 + @CumPerf), (@PeriodBase / @PeriodCount)) - 1
)
RETURN @Result
END
- B
-
quote:
Sharpe and Treynor = huh?
Keep wishing, you never receive a request to implement this!
Quite dry theoretical, statistical stuff, but one of the major point when is comes to performance and mutual funds.
BTW, what runtime or reaction time is this expected to have?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Reaction time does not matter as their other tecnologies act on a lag that my Grandmother couldn't stand - so it's free form.
However, My current system runs like the wind and can calculate and serve formatted HTML for 20 funds, 9 time series(Daily, Week To date, Month to date...), for a five year period in under a second - no problems there.
The main display query makes sweet usage of several correlated subqueries - one for each Fund per time period. UDF's provide appropriate end and begin dates (MTD, YTD, etc..) and 1 main UDF calcultes the Performance based on FUnd ID, Begin date and End date.
I cannot beleive the performance!!!!
Looks something like this...
SELECT
1 AS SortOrder,
'F' AS EntityTyp,
MUF.id_MutualFund AS id_Entity,
MUF.FundNme AS EntityNme,
NAVAmt = dbo.ToNAVarcharDisplay(HFUN.NAVAmt),
Daily = dbo.ToNAVarcharDisplay(dbo.peGetFundPerformance
(MUF.id_MutualFund, @Dater, @Dater) * 100),
CurWeek = dbo.ToNAVarcharDisplay(dbo.peGetFundPerformance
(MUF.id_MutualFund, dbo.GetFirstMondayFromDate(@Dater), @Dater) * 100),
PrevWeek = dbo.ToNAVarcharDisplay(dbo.peGetFundPerformance
(MUF.id_MutualFund, dbo.GetFirstMondayFromDate(DATEADD(wk, -1, @Dater)), DATEADD(d, 4, dbo.GetFirstMondayFromDate(DATEADD(wk, -1, @Dater)))) * 100),
MonthToDate = dbo.ToNAVarcharDisplay(dbo.peGetFundPerformance
(MUF.id_MutualFund, dbo.GetFirstDayDateInMonth(@Dater), @Dater) * 100),
<snip>
- B
-
Hi BillyWilly,
quote:
However, My current system runs like the wind and can calculate and serve formatted HTML for 20 funds, 9 time series(Daily, Week To date, Month to date...), for a five year period in under a second - no problems there.
have you ever considered a marketing or sales job
So what are you worring about scale?
quote:
I cannot beleive the performance!!!!
Of your app or of your fund managers?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Worrying 99% about accuracy of performance calcs.
The performance thing is only a sideline thinking about the future and things like 10 year performance etc...
1) Must work
2) Must work accurately
3) Must work fast
- B
-
quote:
Worrying 99% about accuracy of performance calcs.The performance thing is only a sideline thinking about the future and things like 10 year performance etc...
1) Must work
2) Must work accurately
3) Must work fast
No need to worry! You can simply check this with Excel or ask a Fund Manager or someone else to verify the result before they move into production.
Who knows where you work in five years
At least you have five years remaining thinking about a fix. Maybe you should consider rolling out Service Packs. ROTFL!!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply