Calculating covariance from a set of data

[ollisalo]

Hi

Firstly I'm quite a newbie in SQL, so apologies for any obvious stupid questions.

My problem is that I have a large set of stock price data for different companies together with the stock market price index, and I wish to calculate the covariance between the stock price and the index.

The data is structured so that for each row I have
PERMNRO - unique id for given company as integer
DATE - in text format yyyymmdd as text (not date, so some conversion needed)
VALUECHANGE - change in company's market price as double
INDEXCHANGE - change in the corresponding index as double

There is data for each banking day for 600 companies for ten years, so the database is quite large.

My output table would ideally be
PERMNRO
YEAR
COVARIANCE FOR LAST 3 YEARS
having an entry for the 31st of 12 december each year for each company.

I'm SLOWLY constructing the appropriate SQL stament in Access SQL view. I realize I need nested select statements, but just can not get the syntax correct.

Hopefully this description is clear and some of the experts could help me out. Although I have some background in programming, this whole stock price covariance thing is too complicated for me. Normally all the strategy research things I'm involved with here at the University are solved with Excel or VBA macros in extreme cases...

Best regards and greetings from Finland,

Olli Salo

[NeoPa]

I have a little routine for converting your dates to DateTime format

'YMDToDate takes varDate in YYYYMM or YYYYMMDD format and returns it as a date.
'28/01/2004 Return Null if not valid format.
Public Function YMDToDate(varDate As Variant) As Variant
    Dim strDMY As String
 
    strDMY = Mid(varDate, 5, 2) & "/" & Left(varDate, 4)
    If Len(varDate) = 8 Then strDMY = Mid(varDate, 7, 2) & "/" & strDMY
    YMDToDate = Null
    If IsDate(strDMY) Then YMDToDate = CDate(strDMY)
End Function
 

I don't know how to calculate covariance mind.
Can you express what you would like the base data to be like - I may be able to provide SQL to get the data needed.

[Tanis]

Covariance in financial terms is different from statistical covariance. Just be aware.

[ollisalo]

Hi

I mean the normal mathematical covariance, as defined for example by Wikipedia (http://en.wikipedia.org/wiki/Covariance).

In essence can be calculated with sum operator.

BR

Olli Salo