January 18, 2012 at 9:03 am
Hi All,
I've got a situation where I need to calculate the one [business] day return of a certain fund, which is basically a given day's price / the prior days price. The issue I'm struggling with is that if there is a big gap between one day and the last day the fund traded (i.e. more than one business day), the calculation needs to return null.
I have a calendar table which shows the trading dates for the funds exchange, and need to use that to say "if there's a date where the exchange traded between the current date of the fund and the last time the fund traded, set the one day return to null. Here's some mock code illustrating the issue:
if object_id('tempdb.dbo.#Calendar') > 0 drop table #Calendar
create table #Calendar
(
Exchange varchar(20),
TradeDate int
primary key clustered (Exchange, TradeDate)
)
if object_id('tempdb.dbo.#NAV') > 0 drop table #NAV
create table #NAV
(
FundID int,
NAVPrice float,
NAVDate int,
Exchange varchar(20),
OneDayReturn float
)
if object_id('tempdb.dbo.#Exchanges') > 0 drop table #Exchanges
create table #Exchanges
(
RID int identity(1,1) primary key clustered,
Exchange varchar(32)
)
insert into #Calendar(Exchange, TradeDate)
select 'ABC', 40000 union
select 'ABC', 40001 union
select 'ABC', 40004 union
select 'ABC', 40005 union
select 'ABC', 40006 union
select 'XYZ', 40001 union
select 'XYZ', 40002 union
select 'XYZ', 40003
insert into #NAV (FundID, NAVPrice, NAVDate, Exchange)
select 1, rand() * 100, 40000, 'ABC' union
select 1, rand() * 100, 40001, 'ABC' union
select 1, rand() * 100, 40006, 'ABC' union
select 2, rand() * 100, 40001, 'ABC' union
select 2, rand() * 100, 40002, 'ABC'
insert into #Exchanges (Exchange)
select distinct Exchange
from #Calendar
;with nav as
(
select
row_number() over (partition by FundID order by NAVDate desc) as RID,
*
from #NAV
)
update a
set OneDayReturn = a.NAVPrice / nullif(b.NavPrice, 0) - 1
from nav a
left outer join nav b
on a.RID = b.RID - 1
select *
from #Calendar
select *
from #Exchanges
select *
from #NAV
Any thoughts? I've hit a wall.
Thanks
January 18, 2012 at 11:06 am
your explanation is a bit unclear as why you want NULLs. can't you just eliminate the possibility of generating NULL's??
if object_id('tempdb.dbo.#Calendar') > 0 drop table #Calendar
create table #Calendar(Exchange varchar(20),TradeDate int primary key clustered (Exchange, TradeDate))
if object_id('tempdb.dbo.#NAV') > 0 drop table #NAV
create table #NAV(FundID int, NAVPrice float, NAVDate int, Exchange varchar(20), OneDayReturn float)
if object_id('tempdb.dbo.#Exchanges') > 0 drop table #Exchanges
create table #Exchanges(RID int identity(1,1) primary key clustered, Exchange varchar(32))
insert into #Calendar(Exchange, TradeDate)
select 'ABC', 40000 union
select 'ABC', 40001 union
select 'ABC', 40004 union
select 'ABC', 40005 union
select 'ABC', 40006 union
select 'XYZ', 40001 union
select 'XYZ', 40002 union
select 'XYZ', 40003
insert into #NAV (FundID, NAVPrice, NAVDate, Exchange)
select 1, rand() * 100, 40000, 'ABC' union
select 1, rand() * 100, 40001, 'ABC' union
select 1, rand() * 100, 40002, 'ABC' union
select 1, rand() * 100, 40003, 'ABC' union
select 1, rand() * 100, 40004, 'ABC' union
select 1, rand() * 100, 40006, 'ABC' union
select 2, rand() * 100, 40001, 'XYZ' union
select 2, rand() * 100, 40002, 'XYZ'
insert into #Exchanges (Exchange)
select distinct Exchange from #Calendar
declare @nav table (Exchange varchar(3), Yesterday int, YesterdaysPrice float, Today int, TodaysPrice float,OneDayReturn float)
insert @nav
select T1.Exchange, T3.NAVDate Yesterday,T3.NAVPrice YesterdaysPrice,
T2.NAVDate Today,T2.NAVPrice TodaysPrice,T2.OneDayReturn
from #Calendar T1 JOIN #NAV T2 ON T1.TradeDate = T2.NAVDate -1 and T1.Exchange = T2.Exchange
JOIN #Nav T3 on T2.NAVDate -1 = T3.NAVDate
update @nav
set OneDayReturn = (YesterdaysPrice / TodaysPrice)
select * from @nav
January 18, 2012 at 11:19 am
disregard my previous post. I see what you are after now... I will get back to you.
January 18, 2012 at 1:27 pm
ok, try this one. I used your same tmp tables and logic, i only changed your cte into a table variable for the update and changed the join to compare the RID a bit different.
if object_id('tempdb.dbo.#Calendar') > 0 drop table #Calendar
create table #Calendar(Exchange varchar(20),TradeDate int primary key clustered (Exchange, TradeDate))
if object_id('tempdb.dbo.#NAV') > 0 drop table #NAV
create table #NAV(FundID int, NAVPrice float, NAVDate int, Exchange varchar(20), OneDayReturn float)
if object_id('tempdb.dbo.#Exchanges') > 0 drop table #Exchanges
create table #Exchanges(RID int identity(1,1) primary key clustered, Exchange varchar(32))
insert into #Calendar(Exchange, TradeDate)
select 'ABC', 40000 union
select 'ABC', 40001 union
select 'ABC', 40004 union
select 'ABC', 40005 union
select 'ABC', 40006 union
select 'XYZ', 40001 union
select 'XYZ', 40002 union
select 'XYZ', 40003
insert into #NAV (FundID, NAVPrice, NAVDate, Exchange)
select 1, rand() * 100, 40000, 'ABC' union
select 1, rand() * 100, 40001, 'ABC' union
select 1, rand() * 100, 40002, 'ABC' union
select 1, rand() * 100, 40006, 'ABC' union
select 2, rand() * 100, 40001, 'ABC' union
select 2, rand() * 100, 40002, 'ABC'
insert into #Exchanges (Exchange)
select distinct Exchange from #Calendar
select * from #Calendar
--select * from #Exchanges - I never did see a reason for this???
declare @nav table (RID int, FundID int, NAVPrice float, NAVDate int, Exchange varchar(3), OneDayReturn float)
insert into @nav
select ROW_NUMBER() over (partition by FundID order by NAVDate) as RID,*
from #NAV
update a
set OneDayReturn = a.NAVPrice/ b.NAVPrice
from @nav a
join @nav b
on a.RID-1 = b.RID and a.FundID = b.FundID
select * from @nav
January 18, 2012 at 7:13 pm
Thanks for the response Geoff. To your initial question about the nulls, part of the reason for wanting to make the returns null was that if say, stock A trades very infrequently, the two "adjacent" trading dates might be today, and 3 weeks ago. In that case, doing today / 3 weeks ago isn't really an accurate representation of a one day return.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply