July 10, 2012 at 2:52 pm
Hello,
I am using sql to manage data, and it would be very helpful if anyone is familiar with an efficient way to calculate Sharpes Ratio, as well as other summary statistics such as standard deviation, and Sorentino's Ratio for a large data set.
Thanks very much; all input welcome as I am an inexperienced programmer.
July 10, 2012 at 3:20 pm
Something like this?
CREATE TABLE #temp(subject_id int identity(1,1), measurement numeric(8,2))
INSERT INTO #temp (measurement)
SELECT 5.0
UNION ALL
SELECT 6.2
UNION ALL
SELECT 4.6
DECLARE @mean numeric(8,2)
DECLARE @ss numeric(8,2)
DECLARE @n numeric(8,2)
DECLARE @stdDev numeric(8,2)
SET @n = (SELECT COUNT(1) FROM #temp);
SET @mean = (SELECT SUM(measurement) FROM #temp) / @n;
SET @ss = (SELECT SUM(POWER(measurement - @mean,2)) FROM #temp)
SET @stdDev = SQRT(@ss / (@n - 1.0))
SELECT @mean
SELECT @ss
SELECT @n
SELECT @stdDev
Jared
CE - Microsoft
July 11, 2012 at 10:33 am
looks great! thanks very much for your help. Ill work with this and let you know.
Take care,
CJ
May 16, 2016 at 5:45 pm
Thank you for this code, I have been trying to find sql solutions for stock analysis.
i have been tinkering around with the example and I wanted to say thank you for posting it..
one question.. how would i correctly replace the union select statements to grab a list of closing prices from my database instead of the hard coded numbers in the example
My table is called STKHIST and the Closing price is labeled ClosePr and date is labeled CloseDate.
Here is what I have so for.. any help would be greatly appreciated!
----------------------------------------------------
-- spsharpcalcA nflx
ALTER PROC [dbo].[spsharpcalcA]
@symbol varchar(10)
AS
CREATE TABLE #temp(subject_id int identity(1,1),closedate date, ClosePr numeric(8,2),Symbol varchar(5))
INSERT INTO #temp (closedate,ClosePr,symbol)
SELECT a.closedate,a.closepr,a.symbol from STKHIST a where a.Symbol=@symbol
--UNION ALL
--SELECT b.closedate,b.closepr,b.symbol from STKHIST b where b.Symbol=@symbol
DECLARE @mean numeric(8,2)
DECLARE @ss numeric(8,2)
DECLARE @n numeric(8,2)
DECLARE @stdDev numeric(8,2)
SET @n = 252 --(SELECT COUNT(1) FROM #temp);
SET @mean = (SELECT SUM(ClosePr) FROM #temp) / @n;
SET @ss = (SELECT SUM(POWER(ClosePr - @mean,2)) FROM #temp)
SET @stdDev = SQRT(@ss / (@n - 1.0))
SELECT @mean as mean
, @ss as ss
, @n as n
,@stdDev as stddev
--drop table
select * from dbo.#temp
-----------------------------------------------------
Regards, and thank you!
Bill Ford
May 16, 2016 at 9:29 pm
Another way without variables.
SELECT
mean,
ss = SUM(POWER(measurement - mean,2)),
n,
stdDev = SQRT(SUM(POWER(measurement - mean,2)) / (n - 1.0))
FROM #temp
CROSS JOIN
(
SELECT mean = SUM(measurement) / COUNT(*), n = COUNT(*)
FROM #temp
) prep
GROUP BY mean, n;
Using variables it will perform much better you could get even better performance turning the logic above into an indexed view. Just a thought.
-- Itzik Ben-Gan 2001
May 16, 2016 at 9:51 pm
Alan,
Thank you very much!
I am looking at/absorbing this now.
Thanks again
Bill
May 17, 2016 at 6:54 am
I'm a bit confused at what Alan did there, because #temp simply holds the raw data, it is not calculated... Anyway, my UNION ALL was just to build a table with data in it, You already have that. Therefore, to do calculations you would just do it from your parent table. I have, for example, created a temp table to simulate your actual table.
CREATE TABLE #STKHIST
(
Subject_ID INT IDENTITY(1, 1) ,
closedate DATE ,
ClosePr NUMERIC(8, 2) ,
Symbol VARCHAR(5)
);
INSERT INTO #STKHIST
( closedate ,
ClosePr ,
Symbol
)
SELECT '2016-05-12' ,
90.34 ,
'AAPL'
UNION ALL
SELECT '2016-05-13' ,
90.52 ,
'AAPL'
UNION ALL
SELECT '2016-05-16' ,
93.88 ,
'AAPL'
UNION ALL
SELECT '2016-05-12' ,
51.51 ,
'MSFT'
UNION ALL
SELECT '2016-05-13' ,
51.08 ,
'MSFT'
UNION ALL
SELECT '2016-05-16' ,
51.83 ,
'MSFT';
;
WITH cte
AS ( SELECT Symbol ,
COUNT(1) AS nClosePr ,
SUM(ClosePr) AS sumClosePr ,
AVG(ClosePr) AS meanClosePr
FROM #STKHIST
GROUP BY Symbol
)
SELECT cte.Symbol ,
cte.nClosePr ,
cte.sumClosePr ,
cte.meanClosePr ,
SUM(POWER(STKHIST.ClosePr - cte.meanClosePr, 2)) AS ssClosePr ,
SQRT(SUM(POWER(STKHIST.ClosePr - cte.meanClosePr, 2))
/ MAX(( cte.nClosePr - 1.0 ))) AS stdevClosePr
FROM cte
INNER JOIN #STKHIST STKHIST ON cte.Symbol = STKHIST.Symbol
GROUP BY cte.Symbol ,
cte.nClosePr ,
cte.sumClosePr ,
cte.meanClosePr;
Jared
CE - Microsoft
May 17, 2016 at 7:38 am
Are we all aware that SQL Server includes the STDEV and VAR functions?
Other than the nulls being ignored, it should be easier to use.
May 17, 2016 at 7:51 am
Luis Cazares (5/17/2016)
Are we all aware that SQL Server includes the STDEV and VAR functions?Other than the nulls being ignored, it should be easier to use.
1. I was not, its been awhile since I've had to do any statistics or BI work. 2. Isn't this so much more fun?! 🙂
Jared
CE - Microsoft
May 17, 2016 at 9:50 am
SQLKnowItAll (5/17/2016)
Luis Cazares (5/17/2016)
Are we all aware that SQL Server includes the STDEV and VAR functions?Other than the nulls being ignored, it should be easier to use.
1. I was not, its been awhile since I've had to do any statistics or BI work. 2. Isn't this so much more fun?! 🙂
It's fun to write it, but not to review it months later. 😀
May 17, 2016 at 10:44 am
Luis Cazares (5/17/2016)
Are we all aware that SQL Server includes the STDEV and VAR functions?Other than the nulls being ignored, it should be easier to use.
I know about them but have not played around with them but need to start.
-- Itzik Ben-Gan 2001
May 17, 2016 at 2:57 pm
jared,
Thank you.. this makes sense..
Thanks for taking the time to help!!
Regards
Bill
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply