Friday, April 08, 2011

Statistics in SQL and Finance

Correlation is important when dealing with risk management or forecasting calculations.  The closer to 1 you are, the closer the numbers are correlated.  Consider if you purchase GOOG and MSFT stock.  Since they are both in the tech sector, they could be part of a combined selloff, which means your risk is increased if you own both in your portfolio.

The Select Sector SPDRs Correlation Tracker identifies GOOG as being 0.46 correlated to MSFT, which appears low. 

You can use Correlation for forecasting results and determining trends in SQL.  Consider this query:

SELECT (COUNT(*)*SUM(x.Sales*y.Sales)-SUM(x.Sales)*SUM(y.Sales))/( SQRT(COUNT(*)*SUM(SQUARE(x.Sales))-SQUARE(SUM(x.Sales)))* SQRT(COUNT(*)*SUM(SQUARE(y.Sales))-SQUARE(SUM(y.Sales)))) correlation FROM BulbSales x JOIN BulbSales y ON x.month=y.month WHERE x.Year=1997 AND y.Year=1998

Transact-SQL Cookbook: Chapter 8: Statistics in SQL

If the correlation is closer to 1, the numbers are similar.  Comparing various years and months of sales or expenses can easily uncover correlations.  This could also work for tracking correlation dependencies between expenses and revenue.

SQL has a number of formulas that are useful for identifying patterns and trends in your data.

No comments: