By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,373 Members | 1,763 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,373 IT Pros & Developers. It's quick & easy.

Calculating covariance from a set of data

P: 2
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
Oct 26 '06 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,436
I have a little routine for converting your dates to DateTime format

Expand|Select|Wrap|Line Numbers
  1. 'YMDToDate takes varDate in YYYYMM or YYYYMMDD format and returns it as a date.
  2. '28/01/2004 Return Null if not valid format.
  3. Public Function YMDToDate(varDate As Variant) As Variant
  4.     Dim strDMY As String
  5.  
  6.     strDMY = Mid(varDate, 5, 2) & "/" & Left(varDate, 4)
  7.     If Len(varDate) = 8 Then strDMY = Mid(varDate, 7, 2) & "/" & strDMY
  8.     YMDToDate = Null
  9.     If IsDate(strDMY) Then YMDToDate = CDate(strDMY)
  10. End Function
  11.  
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.
Oct 26 '06 #2

100+
P: 143
Covariance in financial terms is different from statistical covariance. Just be aware.
Oct 26 '06 #3

P: 2
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
Oct 26 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.