Excel 2007... importing intraday data and calculate correlation

monty21 · Apr 28, 2009

Quote from GTG:

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
More...
Wow thanks a lot... I'm running it now

I would've never been able to write this entire code myself.

This was my code for which I found by using the macro recorder:

Sub ImportingWebData()
'
' ImportingWebData Macro
' Importing Web Data
'
' Keyboard Shortcut: Ctrl+Shift+D
'
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.google.com/finance?q=spy", Destination:=Range("$A$1"))
.Name = "finance?q=spy"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """md"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

monty21 · Apr 28, 2009

Here are DTrader98 findings from the macro recorder:

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

Dim qt As QueryTable
For Each qt In ActiveSheet.QueryTables
qt.Refresh BackgroundQuery:=False
Next

Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("A" & i).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("A1").Select
i = i + 1

End Sub

**************************

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

Static i As Integer

Selection.Copy
Sheets("Sheet2").Select
Range("A" & i + 1).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("A1").Select
i = i + 1

Dim qt As QueryTable
For Each qt In ActiveSheet.QueryTables
qt.Refresh BackgroundQuery:=False
Next

End Sub

monty21 · Apr 28, 2009

Thanks GTG and DTrader98...

I'm going to run both codes tomorrow and will post the results on the thread.

GTG · Apr 28, 2009

Do you know if the quotes on google finance are real-time or are they delayed quotes?

EdgeHunter · Apr 28, 2009

Quote from monty21:

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?
More...

Yes, there is Excel VBA code to save the first quote of say "SPY" to cell D1 & "USO" into cell E1 ... then save the next quote 1 second or 10 seconds or whatever X seconds later to cell D2 and E2... etc.. all the way out to whatever cell... or you can do it vertically rather than horizontally...

Pretty straight forward...

You can even save the data to a TXT file or to an Access database.. etc...

...

monty21 · Apr 28, 2009

Quote from GTG:

Do you know if the quotes on google finance are real-time or are they delayed quotes?
More...

Google quotes are real-time for NYSE, NASDAQ, AMEX listings (provided by BATS ecn)...

When I develop the other workbooks for my overall strategy, I will subscribe to a more professional data feed.

EdgeHunter mentioned that I could get quotes for ~$20 so I may get the feed very soon. Earlier I assumed that I would have to pay NYSE and NASDAQ ~$200 a month each. Glad to see that I was wrong. Fortunately I just need Level I.

monty21 · Apr 28, 2009

Quote from EdgeHunter:

Yes, there is Excel VBA code to save the first quote of say "SPY" to cell D1 & "USO" into cell E1 ... then save the next quote 1 second or 10 seconds or whatever X seconds later to cell D2 and E2... etc.. all the way out to whatever cell... or you can do it vertically rather than horizontally...

Pretty straight forward...

You can even save the data to a TXT file or to an Access database.. etc...

...
More...

I sent an e-mail to their sales department earlier tonight. Had a couple of questions.

Looking forward to hearing from them tomorrow. It would be a big relief if I don't have to cough up $350 a month. I don't need Level II for my system.

frankkej · Feb 19, 2015

GTG said:
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
More...
What portion of this code generates the random numbers and how do i replace that part of the code to read active stock prices? the cell formula for active pricing is =RTD("tos.rtd", , "LAST", A1) with A1 being the ticker symbol

minmike · Mar 12, 2015

I love someone using search! I'm not sure how to do it that way. When I do that, I use the application on time now + time. should be easy enough to find. I record a macro to insert a line, moving all previous data down and paste values the new data. I hope that helps.