6. Relational Database Diagram


6.1 SQL queries for Sharpe’s Model Coefficients

Every time new data arives the coeficients that are stored in the database should be adjusted. This can be implemented using trigers in a traditional database system. By now these values are precalculated, becase we have a static data sample.

Queries Used:

IndexData

SELECT Index.*

FROM [Index]

WHERE (((Index.Date)>[lastDate]) AND ((Index.Symbol)="CSE Gen."))

ORDER BY Index.Date;

RegrCoef

SELECT Sum(([TickerHistory].[ChangeDailyPercent]*[IndexDataChangeDailyPercent])) AS sumxy, Sum(([IndexDataChangeDailyPercent]*[IndexDataChangeDailyPercent])) AS sumx2, Sum([TickerHistory].[ChangeDailyPercent]) AS sumy, Sum(TickerIndexSharpeDate.IndexDataChangeDailyPercent) AS sumx, TickerIndexSharpeDate.IDTicker

FROM TickerIndexSharpeDate

GROUP BY TickerIndexSharpeDate.IDTicker;

TrendDefaulSigma

SELECT ([sumy]/[sumx]-[sumxy]/[sumx2])/([NrDays]/[sumx]-[sumx]/[sumx2]) AS [default], ([sumy]-[NrDays]*[default])/[sumx] AS trend, regrCoef.IDTicker

FROM regrCoef;

TickerIndexSharpeDate

SELECT TickerHistory.Closing, TickerHistory.ChangeDailyPercent, TickerHistory.Date, TickerHistory.IDTicker, IndexData.Date, IndexData.ChangeDailyPercent AS IndexDataChangeDailyPercent

FROM IndexData INNER JOIN TickerHistory ON IndexData.Date = TickerHistory.Date

WHERE (((IndexData.Date)>[lastDate]));

StdDevEpsilnSigma

SELECT Var(Sigma.Epsilon) AS VarOfEpsilon, Sigma.TickerIndexSharpeDate.IDTicker

FROM Sigma

GROUP BY Sigma.TickerIndexSharpeDate.IDTicker;

Final

SELECT TrendDefaultSigma.default, TrendDefaultSigma.trend, Sqr([VarOfEpsilon]) AS Sigma, TrendDefaultSigma.IDTicker

FROM stdDevEpsilonSigma INNER JOIN TrendDefaultSigma ON stdDevEpsilonSigma.IDTicker = TrendDefaultSigma.IDTicker;

IndexData

SELECT Index.*

FROM [Index]

WHERE (((Index.Date)>[lastDate]) AND ((Index.Symbol)="CSE Gen."))

ORDER BY Index.Date;

MarketValuesIndx

SELECT Avg(IndexData.ChangeDailyPercent) AS AvgOfChangeDailyPercent, Sqr(Var([ChangeDailyPercent])) AS Expr1

FROM IndexData;


SourceForge Logo