Excel 2007... importing intraday data and calculate correlation

I am developing a high frequency trading system and am having trouble overcoming one obstacle: importing quotes (every 10 seconds) and having them stored so I could calculate correlation between two stocks (essentially the S&P 500 index and a stock or ETF).

I know how to import data from the web and am familiar with the CORREL function. I plan to record the prices of SPY and USO every 10 seconds (I know that you are only able to refresh data every 1-minute in Excel, but I have a VBA code that refreshes it every 10 seconds), and then use the CORREL function to see if oil is trading with the S&P futures. (I know this can be easily seen on a chart, but I will have many other variables and desire to quantify the whole process).

Doe's anyone know how I can save the data in a cell every 10 seconds so that I can calculate the correlation. Is this only possible using VBA?

Any thoughts on different ways of approaching this would be greatly appreciated.

 

Attach your 10 sec refresh capture code (make sure to include how the captured data is being sent to excel); if I get a chance I'll modify the vba to do what you want.

Seems pretty simple once you have the data captured. Just run a loop and write out the result of the captured value to a cell indexed by the loop iteration value.

P.S. I don't run 07 (03), but the code should work on both platforms; the newer excel may by a bit more proficient on isolating captured queries though.

 

I found this code sample that will loop and refresh my data every 10 seconds. Besides that everything else is in simple EXCEL and not VBA...

Sub Start_Timer()
Application.OnTime Now + TimeValue("00:00:10"), "Refresh_Query"
End Sub

Sub Refresh_Query()
Sheets("myquerysheet").Activate
Range("a1").Select
Selection.QueryTable.Refresh BackgroundQuery:=True
Start_Timer
End Sub

My bigger challenge is getting the data into a new cell each time and recording it. Without that I cannot do the correlation analysis (CORREL).

 

I'm not seeing the entire picture, but tell me if this sounds correct. Currently, it is simply updating and overwriting cell a1 with the latest data as it streams in.
Is that correct? If so, does it overwrite any other cells? Try to substitute Range("a1") with Cells(1,"A") and see if that does that same thing. If that works,
try for now:


Sub Start_Timer()

Application.OnTime Now + TimeValue("00:00:10"), "Refresh_Query"
End Sub

Sub Refresh_Query()
i=i+1
Sheets("myquerysheet").Activate
Cells(i,"A").Select
Selection.QueryTable.Refresh BackgroundQuery:=True
Start_Timer
End Sub

what happens? Also, can you give an idea of what is in cell a1 that is performing the query in your excel spreadsheet?

 

Thanks for the quick reply... Tomorrow I will test it during market hours when the quotes refresh continuously.

And yes, whenever the quotes refresh, they are in the same cell. I'll take your advice w/ the changes and let you know tomorrow how it pans out.

Thanks a lot, I appreciate it.

 

I'm not certain if i needs to be initialized on each run; it would help a lot if I had the spreadsheet to execute and debug, but just see if this works for now.

Good Luck.

 

Now or down the road you could use a DDE feed to Excel to get 4 quotes a SECOND in each cell for each symbol you want to track

MarketFeed has an excellant DDE feed to Excel for only $23 dollars a month... 300 symbols max... NYSE, Nasdaq, AMEX... exchanges...

just a thought... HtH...

 

Hi dtrader98,

The loop macro works great for refreshing data. Unfortunately the recording of the data does not work.

I composed a sheet in Excel so you can maybe better understand my goal. There is no macro in it because I couldn't attach it on ET with that format. Besides its the same as the earlier post... you could just copy paste the code into VBA. I did change the time loop to 30 seconds though:

Sub Start_Timer()
Application.OnTime Now + TimeValue("00:00:30"), "Refresh_Query"
End Sub

Sub Refresh_Query()
Sheets("Sheet1").Activate
Range("a1").Select
Selection.QueryTable.Refresh BackgroundQuery:=True
Start_Timer
End Sub

In order to do my correlation analysis I need to find a way to record the prices of my two stocks/ETFS (as an example, SPY represents the S&P 500 futures and TLT represents the longer-term bond market). I need to record both prices into different cells so I could do the calculation. As of now I'm stuck because every time the loop refreshes, I don't know how to record that price into another cell.

When you open my Excel file, you will make more sense of it. I tried my best to clarify what I want to do with my descriptions in the cells.

I greatly appreciate your time and effort.

P.S. I am using EXCEL 2007, but had to save the file in 97-2003 format in order to post it here on ET... Hopefully it will be the same... If you would like, PM me with your e-mail address and I'll send you the .xlsx one

 

Really, $23 a month for real-time prices? 300 tickers is more than I need too. Sounds great.

Do you know if it is possible to record the price of the same stock (every 30 seconds or so) into different cells so I could run the CORREL function between two different stocks?

 

Here is code that should do what you need.

Code:

Sub Start_Timer()
    Application.OnTime Now + TimeValue("00:00:30"), "Refresh_Query"
End Sub

Sub Refresh_Query()
    Sheets("Sheet1").Activate
    Range("a1").Select
    Selection.QueryTable.Refresh BackgroundQuery:=True

    ' cell location for the sample counter number
    Dim sampleCounterCell As Range
    Set sampleCounterCell = Range("Sheet1!C2")
    
    ' save a timestamp
    SaveSampleTimeStamp sampleCounterCell, Range("Sheet2!B3")
    
    ' save price samples for stock 1 & 2
    SaveSample sampleCounterCell, Range("Sheet1!C4"), Range("Sheet2!C3")    ' stock 1
    SaveSample sampleCounterCell, Range("Sheet1!C5"), Range("Sheet2!D3")    ' stock 2
    
    'set up for next sample
    IncrementSample sampleCounterCell
    
    Start_Timer
End Sub

Sub SaveSampleTimeStamp( _
    sampleCounterCell As Range, _
    timeStampFirstCellInColumn As Range _
    )
    
    Dim currentSampleNum As Long
    currentSampleNum = sampleCounterCell.Value
    
    Dim timeStampTarget As Range
    Set timeStampTarget = timeStampFirstCellInColumn.Offset(currentSampleNum)
    
    timeStampTarget.Value = Now()
End Sub


Sub SaveSample( _
    sampleCounterCell As Range, _
    sampleSourceCell As Range, _
    sampleTargetFirstCellInColumn As Range _
    )
    
    Dim currentSampleNum As Long
    currentSampleNum = sampleCounterCell.Value
    
    Dim currentSample As Variant
    currentSample = sampleSourceCell.Value
    
    Dim sampleTarget As Range
    Set sampleTarget = sampleTargetFirstCellInColumn.Offset(currentSampleNum)
    
    sampleTarget.Value = currentSample
End Sub

Sub IncrementSample(sampleCounterCell As Range)
    Dim currentSampleNum As Long
    currentSampleNum = sampleCounterCell.Value
    currentSampleNum = currentSampleNum + 1
    sampleCounterCell.Value = currentSampleNum
End Sub

Here is how it works.

(1) Choose a cell on your worksheet to hold the sample count.
(2) Modify this line of code with the cell location for the sample count:

Code:

Set sampleCounterCell = Range("Sheet1!C2")

In my example. I am storing the sample count in cell C2 on Sheet1
(3) In the morning before you start the macro, you need to set the value in the sample count cell to 0
(4) Choose a location to store your data samples for the day. In my example I have chosen to store the data on Sheet2.
(5) Modify the macro to point to your sample source cells and sample target cells:

Code:

SaveSampleTimeStamp sampleCounterCell, Range("Sheet2!B3")
    SaveSample sampleCounterCell, Range("Sheet1!C4"), Range("Sheet2!C3")
    SaveSample sampleCounterCell, Range("Sheet1!C5"), Range("Sheet2!D3")

In my example The prices of the 2 stocks are on Sheet1 in cells C4 and C5 respectively.
I store the data in column format on Sheet2 in columns B3, C3, and D3, where the column starting at B3 holds the time stamps, the column starting at C3 holds the prices for stock1 and the column starting at D3 holds the prices for stock2.
(6) At the end of the day, save the data in Sheet2 to a location of your choosing. Don't forget to reset the sample counter back to 0 for the next day.
(7) Add Additional calls to the "SaveSample" subroutine to save additional stock price samples

I have attached an excel file that demos how this works, using random values for the stock maret data since I don't have your code for downloading real-time prices