April 3, 2003 at 2:24 pm
Hey - Another financial question.
ENTITY DAY NAV PERFORMANCE
S&P 500 4/1/2003 1000 .00
DJIA 4/1/2003 5000 0
S&P 500 4/2/2003 1100 .10
DJIA 4/2/2003 5000 0
S&P 500 4/3/2003 1000 -.09
etc..
The Performance column is calculated and stored by taking Todays NAV Minus Yesterdays NAV divided by Yesterdays NAV.
I've put an INSERT/UPDATE trigger on the table to perform and store the performance - but it only takes into account one row of editing. And worse yet, if someone goes back and edits an NAV from several weeks ago - I need to change ALL the performances from that time forward.
This is going to require alot of tedious coding in my mind versus the basic Excel formulas like taking the Cell minus 1 etc..
Anyone have any suggestions regarding cumulative financial performance and relational databases ????
- B
April 3, 2003 at 6:22 pm
I've seen this situation in a lot of financial companies who were used to using spreadsheets. In my case, it always resulted in a lot of tedious coding, as there was a rollup effect, with each days data being reliant of the previous days.
Another instance of this is in calculating amortizations.....
Sorry I couldn't say different...
April 3, 2003 at 11:31 pm
I don't understand your comment "if someone goes back and edits an NAV from several weeks ago - I need to change ALL the performances from that time forward." According to the calculation, a change in a single NAV only affects the next day's Performance.
As the calculation stands now, how about using a view to do the calculation instead of storing it? To increase (SQL) performance you can use an Insert Trigger on the table to store the PK of the corresponding prior day in the current row (it is easy to write that trigger to handle batches). Then use the stored PK to lookup the row for the calculated column in the view. This way, you don't have to worry about multi-row inserts nor updates to existing rows.
Awe heck, I wanted to see it in action, so here's what I wrote:
create table tbl (
PK int identity(1,1) not null,
Entity varchar(20) null,
Dte datetime null,
NAV decimal(9,0) null,
PreviousPK int null
)
go
create trigger tbl_insert on tbl for insert as
update tbl set PreviousPK=(select top 1 PK from tbl where Entity=i.Entity and PK < i.PK order by PK desc)
from tbl t inner join inserted i on t.PK=i.PK
go
insert tbl (Entity, Dte, NAV) values ('S&P 500','4/1/2003',1000)
insert tbl (Entity, Dte, NAV) values ('DJIA','4/1/2003',5000)
insert tbl (Entity, Dte, NAV) values ('S&P 500','4/2/2003',1100)
insert tbl (Entity, Dte, NAV) values ('DJIA','4/2/2003',5000)
insert tbl (Entity, Dte, NAV) values ('S&P 500','4/3/2003',1000)
select * from tbl
go
create view vw as
select Entity, Dte, NAV, cast((NAV-(select tp.NAV from tbl tp where tp.PK=tc.PreviousPK))/(select tp.NAV from tbl tp where tp.PK=tc.PreviousPK) as decimal(9,2)) as Performance
from tbl tc
go
select * from vw
drop view vw
drop table tbl
Of course, this requires performance testing to see if it is viable.
Jay Madren
Jay Madren
April 4, 2003 at 1:26 am
In the situations I've seen, the values were calculated from the previous days values, so If a value changed two weeks ago, every value from that point forward would have to be re-calculated, based on the formula that was applicable. I would have to loop through them to get this right. Think of it as a rolling total. And since it wasn't simply a sum, but a delta by day divided by the value from the previous day, I would think that would be the case here as well. In my case, we were figuring the percentage of change per day, and the values being stored were sums based on previous days values.
Looking at BillyWilly's numbers, it looks like his situation may be different, and your solution may very well help, though he does mention that it is cumulative values. I just wish it would work for me.
April 4, 2003 at 1:39 am
Hi,
I'm working as an asset manager for an insurance company. We're doing a lot of performance calculations and from my experience this is a classical case for a spreadsheet application. "Dynamic" calculations in a database are a) not so easy to implement b) performance eating c) sometimes not precise (ly enough). I decided to do the basic calculations in my spreadsheets and push the result via macros to a table on the sql server to publish them on our intranet.
Maybe I can help you better, if you tell what you want this all for?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 4, 2003 at 1:41 am
Just a thought, but why use a trigger?
If you are building this from scratch, if you use a stored procedure to do the inserts and updates, you can put the recalculation into the proc.
For an update, you can update the actual row and then update all the subsequent rows:
update tbl
set performance = (a.nav - isnull(b.nav,1))/isnull(b.nav,1)
from tbl a, tbl b
where a.entity = b.entity
and b.dte = dateadd(day,-1,a.dte)
and a.dte > <date of updated row>
If you put all the code into a single transaction it would ensure an update recalculates all the subsequent rows.
NB - the code would not work as the markets are not open as weekends so you need something else for getting the previous value - max(dte). Also, I get an error in QA saying that TBL is ambiguous - you might need to create a temp table or view for the second reference to the table.
Jeremy
April 4, 2003 at 5:14 am
Since the calculations are fairly simple why store them at all?
Why not look into the performance of this:
Your table (tbl):
Day AS DATETIME
NAV AS INT
You make a View:
SELECT TodaysTable.[Day],TodaysTable.[NAV],(TodaysTable.[NAV] - YesterdaysTable.[NAV]) / YesterdaysTable.[NAV] AS PERFORMANCE
FROM tbl AS TodaysTable
INNER JOIN tbl AS YesterdaysTable
ON TodaysTable.[Day] = YesterdaysTable.[Day] + 1
Then you do your queries against the view.
Remeber the indexes for this to be fast, also remember the way SQL Server stores DATETIME (INT+FLOAT, which makes the YesterdaysTable.[Day] + 1 approach possible)
Regards, Hanslindgren!
P.S I noticed that this way is abit similar to Jay Madrens' approach but maybe a bit simpler...
April 4, 2003 at 6:47 am
On a related side note - here's what I'm doing to calculate cumulative performance - Month to Date, Quarter to date etc...
It uses LOG and EXP with SUM rather than multiplication - it's nice.
Anyway - thanks all for the comments.
Regarding why use a Trigger ??
1) I like to keep edits and integrity checks as close to the table as possible - triggers allow me to do this.
2) Mainly - for GUI editing purposes. I was hoping to do a simple and quick Access link to the tables to provide full edit, insert, delete functionality. If I use Stored Procs - the backend calcuations are much easier - but front-end coding becomes complex.
I now see why spreadsheets are SOOOOO popular in the financial world - it's all about performance (no pun intended).
At the moment - I like Jeremys idea of a self join. My big worry now is how SQL Server will handle these type of updates. You know - the dreaded recursive triggering etc...
It's a Pandora's box that hate to open - but may have to now - gulp.
- B
April 4, 2003 at 7:04 am
It is getting REALLY interesting, when you're playing with DDE and realtime quotes and calculations and databases }:-) You can get very easily a server down to its knees. That's why Reuters or Bloomberg recommend using spreadsheets with their applications.
You're speaking of GUI editing purposes, have you ever played with Excel's ODBC Add-in.
I think you can display, modify and save records by using Excel as a front-end
Never looked at this to deeply, but maybe worth a try.
Good luck!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 4, 2003 at 7:14 am
Depending on the size of the table, aren't you limiting concurrency with the storing of your calculations and huge updates? Why not just do the calculations when showing the data? You could put a materialized view if you think that 'online' calculations are hindering your Data Retrieval.
April 4, 2003 at 7:27 am
Hans-
Thanks for the persistence and I'm really starting to like the fully dynamic calculation idea. Going to run some tests now...
- B
April 4, 2003 at 7:57 am
Thanks. For optimal performance, if you are just using dates in your table, consider storing them as INTs and in the view (when displaying them) converting them into DATETIME, in this case you get twice the
amount of IndexRows in one IndexPage (Since an INT occupies 4 bytes versus a DATETIME that stores 8 Bytes) which of course improves greatly.
You can do this if you don't use the Time info (The Float part of the DATETIME). If you cluster on the DATE/INT
field you should maximize the performance in this case (This of course is on the table, if you go for a materialized view,
additional indexes could apply).
Regards, Hanslindgren!
April 4, 2003 at 8:03 am
Ugh-
I may be back to storing and not dynamically calculating.
Why? Because:
1) The perfomance dates are sparse - holidays, weekends etc.
2) I have mutiple entities which have performance in the same table. i.e. Russell 1000, S&P 500, etc...
Doing a self join, account for missing days, and grouping/ordering data to keep all the S&P 500 together versus the Russell 1000 would be a nightmare.
Still working on it....
And BTW Hans - I am in fact storing he date as an INT. This INT is the serialID of a distinct day value which is stored in a big DSS-type table contaning each day from 1925 until 2020. Here's my code for creating that:
CREATE PROCEDURE sys_CreateNewDSSTimeTable AS
SET NOCOUNT ON
if exists (select * from sysobjects where id = object_id('dss_time') and sysstat & 0xf = 3)
drop table dss_time
if not exists (select * from dbo.sysobjects where id = object_id('dbo.dss_time') and sysstat & 0xf = 3)
BEGIN
CREATE TABLE dss_time (
id_Time int IDENTITY (1, 1) NOT NULL ,
FullDatedte smalldatetime NOT NULL ,
DayText varchar (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
MonthTxt varchar (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
YearNum smallint NOT NULL ,
DayOfMonthNum tinyint NOT NULL ,
WeekOfYearNum smallint NOT NULL ,
MonthOfYear tinyint NOT NULL ,
QuarterTxt char (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT PK_dss_Time PRIMARY KEY NONCLUSTERED
(
id_Time
),
CONSTRAINT IXU_dss_Time$FullDate UNIQUE CLUSTERED
(
FullDatedte
)
)
END
DECLARE @Today SmallDateTime
SET @Today = dbo.ToDateOnly('1/1/2002')
DECLARE @Counter int
SET @Counter = 0
WHILE @Counter < 6574 -- select datediff(d, '1/1/2002', '1/1/2020')
BEGIN
INSERT INTO HC_Prod01.dbo.dss_Time
(
FullDatedte,
DayText,
MonthTxt,
YearNum,
DayOfMonthNum,
WeekOfYearNum,
MonthOfYear,
QuarterTxt
)
SELECT
dbo.ToDateOnly(@Today),
DATENAME(dw, @Today),
DATENAME(mm, @Today),
DATEPART(yy, @Today),
DATEPART(d, @Today),
DATEPART(ww, @Today),
DATEPART(m, @Today),
CASE DATEPART(m, @Today)
WHEN 1 THEN 'Q1'
WHEN 2 THEN 'Q1'
WHEN 3 THEN 'Q1'
WHEN 4 THEN 'Q2'
WHEN 5 THEN 'Q2'
WHEN 6 THEN 'Q2'
WHEN 7 THEN 'Q3'
WHEN 8 THEN 'Q3'
WHEN 9 THEN 'Q3'
WHEN 10 THEN 'Q4'
WHEN 11 THEN 'Q4'
WHEN 12 THEN 'Q4'
END
SET @Counter = @Counter + 1
SET @Today = DATEADD(d, 1, @Today)
END
GO
April 4, 2003 at 9:43 am
I believe that you still have to do these considerations when you are calculating your Performance, right? I mean even if you are doing storing, don't you have to take into account the missing days and the Entities?
I don't want to seem over persistent but could it not just be solved by introducing a Holliday field into your dss_Time table and Clustering your Original table with Entity,Day as a composit index?
/H
April 4, 2003 at 10:43 am
Apologies in advance if I'm over-simplifying this (I'm not much on finance).
CREATE TABLE #Performance
(
Entity varchar(40),
ThisDay smalldatetime,
PreviousDay smalldatetime,
Performance float
)
INSERT INTO #Performance
(
Entity,
ThisDay,
PreviousDay
)
SELECT PER1.Entity,
PER1.ThisDay,
Max(PER2.ThisDay) 'PreviousDay'
FROM tblPerformance PER1
LEFT OUTER JOIN
tblPerformance PER2
ON PER1.Entity = PER2.Entity
AND PER1.ThisDay > PER2.ThisDay
GROUP BY PER1.Entity,
PER1.ThisDay
UPDATE tblPerformance
SET Performance = (tblPerformance.NAV - PER2.NAV)/PER2.NAV
FROM tblPerformance
JOIN
#Performance
ON tblPerformance.Entity = #Performance.Entity
AND tblPerformance.ThisDay = #Performance.ThisDay
JOIN
tblPerformance PER2
ON PER2.Entity = #Performance.Entity
AND PER2.ThisDay = #Performance.PreviousDay
SELECT *
FROM tblPerformance
DROP TABLE #Performance
Edited by - TheWildHun on 04/04/2003 10:44:12 AM
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply