You can locate the formulas required for linear regression, and convert those to formulas required.
I had to do this here (I work for a major investment institution) in order to calculate betas (i.e., how closely a portfolio tracks its benchmark) and correlation coefficients (this one's even more fun). It's by no means rocket science; rather, being able to read a little math-geek (aka calculus) and converting it to a relevant expression.
Beta: (Sum([q].[Benchmark]*[q].[Composite])-Sum([q].[Benchmark])*Sum([q].[Composite])/Count(*))/(Sum([q].[Benchmark]*[q].[Benchmark])-Sum([q].[Benchmark])*Sum([q].[Benchmark])/Count(*))
This Beta is a linear regression. The inputs are Benchmark returns and Composite returns. If I have x movement in the benchmark, how much movement do I get in comparison in my portfolio? That's a beta. Take my [Benchmark] and [Composite] fields and substitute them for your values you are attempting to regress against linearly.
In case you care to see correlation of coefficients:
r: ((Sum([a].[Gross]*.[Gross])-Sum([a].[Gross])*Sum(.[Gross])/Count(*)))/(Sqr(CDbl(nz((Sum([a].[Gross]*[a].[Gross])-Sum([a].[Gross])*Sum([a].[Gross])/Count(*))*(Sum(.[Gross]*.[Gross])-Sum(.[Gross])*Sum(.[Gross])/Count(*))))))
In this case, take two distinct gross returns (before fees applied to the investment returns), as identified by [a] and . How well do these correlate? This is interesting in terms of comparing differing investment products to see how the returns compare on a period-by-period basis.
David Atkins, MCP
