Has anyone used MS Access to compute a correlation statistic amou.

  • Thread starter Thread starter Guest
  • Start date Start date
Hi,

If we can use SQL to compute statistics? Sure. FMS inc.
(www.fmsinc.com) has a full library about it, but simple cases can also be
easily written.

More precision in the problem may brought more details in the answer, as
usual.



Hoping it may help,
Vanderghast, Access MVP.
 
Michel
My original question didn't post correctly. What I'm trying to do using an
Access Query, is compute the correlation between two return streams, e.g.,
what is the correlation between the S&P 500 and Russell 2000 index.

If you have any code that can do this with out writing VB, I would be
extremely grateful. The FMS website is subscritpion only and I'm looking to
pay $600 for an add-in.
Regards,
 
Hi,


It may looks like:


SELECT ( COUNT(*)*SUM(tableu.u*tablev.v) - SUM( tableu.u * tablev.v) ) /
( ... ) ^ 0.5
FROM tableu INNER JOIN tablev ON tableu.dateStamp = tablev.dateStamp



where tableU is the first sequence (two fields, u and DateStamp) and
tableV the second sequence. The expression (....) is a place holder for:


(COUNT(*)*SUM(tableu.u^2) -SUM(tableu.u)^2) *
(COUNT(*)*SUM(tablev.v^2) -SUM(tablev.v)^2)



if I take http://mathworld.wolfram.com/CorrelationCoefficient.html Other
formulations are also available (see the reference).


You may also add an offset (even if that is more often meet in hydrology
than in economic), such as adding one year to tableV.dateStamp, in the ON
clause, to see if the two sequence still correlate with recurrence (of one
year), and so on. You may also add a GROUP (again, in hydrology, that can
be GROUP BY tableU.River, and the ON clause would also change:

SELECT tableU.River, (COUNT(*) * ... ) As corrCoef
FROM tableU INNER JOIN tableV ON tableU.DateStamp = tableV.DateStamp AND
tableU.River = tableV.River
GROUP BY tableU.River



Note that any field in the SELECT clause must be either in the GROUP BY
clause, either aggregated:

SELECT SUM(a) * SUM(b), SUM( a* b) , c
FROM ...
GROUP BY c


is fine. Note that fields may be implied in expression before being
aggregated (as in SUM( a * b ) ) or implied in arithmetic after being
aggregated ( as in SUM(a) * SUM(b) ). That being given, if you have the
exact math equation, you should be able to write the SQL statement .




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top