Glad to help. It is definitely doable, although I don't think I'm going to spend much time on it. I suggest learning vba to do that part (particularly the query function). Also, if you are going the cheap route, try looking up the research tool for msn quotes, its free from microsoft and can be downloaded into excel. It has free real time updates, but you have to manage the control of the updates. That is where vba comes in handy.
I've got data coming in from eSignal real time. I'm using this: http://fintools.com/?sigtools I've got it to the point where I have a cell displaying streaming data - Last Price, Hi, Lo, etc. I think what I need to do next is figure out how to make Excel take a "snapshot" of the various streaming data cells for this calculation every 15 minutes, then take those values, and populate the columns with those values. Could I do this with a simple If-Then construction?...something like (very crude form): ------- If CurrentTime=PreviousTime + 15 minutes Then PopulateCells PreviousTime=CurrentTime ------- Just thinking out loud here. I don't really need this application, but I've been meaning to brush up on Excel. I haven't programmed anything for about 6 years. Little rusty. Who knows, maybe this spreadsheet will revolutionize my trading.
probelm solved! (If you have esignal/sigtools that is ). Maybe you guys can try to start a thread showing daily correlations (along with backward evidence of course), to see how effective they are.
Alright, well, I've got it working. I've had it running for a day or so and my R-value as I look at it right now is hovering right around .53. - which was what Brett found to be the historical average. My next puzzle is to figure out what the distribution of the r-value is. Without knowing the standard deviation, it's not very useful. Next step is to assemble as much 15-min historical data on the ETFs as I can and find out some simple statistics that will give meaning to the r-value.