I'm new to SQL and dont think I truly grasp it fully.
My second selected item,after date, gives me (∑r*mv)/(∑mv) which is the weighted returns of the portfolio. The next two SUM columns are attempt to weight the returns based on my selected values.
SELECT
date,
Sum(mv*retmtd)/Sum(mv),
SUM(DISTINCT retmtd*
CASE asset
WHEN 'SP500' then 200000
WHEN 'UST2' then 200000
WHEN 'FTSE' then 600000
END)/1000000,
SUM( retmtd*
CASE
WHEN asset ='SP500' AND mv IS NOT NULL then 200000
WHEN asset ='UST2' AND mv IS NOT NULL then 200000
WHEN asset ='FTSE' AND mv IS NOT NULL then 600000
END)/
SUM( CASE
WHEN asset ='SP500' AND mv IS NOT NULL then 200000
WHEN asset ='UST2' AND mv IS NOT NULL then 200000
WHEN asset ='FTSE' AND mv IS NOT NULL then 600000
END)
FROM [dbo].[AssetRet]
WHERE
asset IN ('SP500','UST2','FTSE')
AND
date >= '12/31/2000'
Is there anyway that I change set the value of mv
manually (to say 200000) over the lifetime so I can see how different weighted portfolios would have performed.
This is supposed to be dynamic and reusable so going creating a new column in the database is unfeasible if I want to do this over and over.
None of my SQL attempts seems to get the correct answer.
The purpose of this exercise is to create synthetic portfolios that would show how a portfolio would have performed if different weights had been given to different asset classes during their life in the portfolio. I hope this makes sense.
e.g instead of mv (market value of the position) being used to weigh that asset in the portfolio I would use a static hypothesized value (200000)
This table is what Sum(mv*retmtd)/Sum(mv) returns which is correct
Date SP500 UST2 FTSE MV MV MV RESULT
3/31/10 0.34 19878 0.34
6/30/10 -0.11 0.12 19954 840712 0.116
6/30/13 -0.48 -1.30 -0.76 9914 934892 384193 -1.135
And this what I want the other querys to retrieve
Date SP500 UST2 FTSE MV MV MV RESULT
3/31/10 0.34 200000 0.34
6/30/10 -0.11 0.12 200000 200000 0.006
6/30/13 -0.48 -1.30 -0.76 200000 200000 600000 -0.809
Is there a way to do this via SQL?
I found a solution to this problem that may be of use to anyone that stumbles across this question.
By wrapping it in another query and using just a case statement in the wrapped query you can modify the data and then do the required data calculations...
SELECT
x.date,
Sum(x.hyp_mv* x.retmtd)/Sum(x.hyp_mv) as weightedreturns
FROM
(
SELECT
date,
asset,
retmtd,
(CASE asset
WHEN 'SP500' then 200000
WHEN 'UST2' then 200000
WHEN 'FTSE' then 600000
END) as hyp_mv
FROM [dbo].[AssetRet]
WHERE
asset IN ('SP500','UST2','FTSE')
AND
date >= '12/31/2000'
)x
GROUP BY
x.date
ORDER BY
x.date