Excel 2013 record/refresh live stock data

Question

I am working in Excel 2013 trying to record live stock data from Yahoo Finance. In my cell A1, I have the GoPro stock symbol GPRO.

Cell B1 has the code

=WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&A1&"&f=l1")

To get the live stock price and in cell C1

=NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&A1&"&f=l1"))

To change the string value gotten from the internet into a number. I have a macro in VBA that is

Sub Capture()
    If I = 0 Then I = 1
    Sheets("recorddata").Cells(1, I) = Sheets("getdata").Range("C1")
    I = I + 1
    Application.OnTime Now + TimeValue("00:00:15"), "Capture"
End Sub

To try to record the live stock price every 15 seconds. For some reason the program will run once and copy the stock price into the next spreadsheet but it will not refresh the stock data or rerun the application every 15 seconds. Can someone please help me find a solution to refreshing the stock data every x amount of time and have the program auto start/stop itself.?

JNevill JNevill · Accepted Answer · 2014-09-30T19:59:18

Two things:

Declare i as a public (Global) variable so it will hold its values between iterations.
Do an Application.Calculate before copying the data to ensure that the =WebService() function makes its call to Yahoo before grabbing the value.

Code:

Public i As Integer

Sub Capture()
    If i = 0 Then i = 1
    Application.Calculate
    Sheets("recorddata").Cells(1, i) = Sheets("getdata").Range("C1")
    i = i + 1
    Application.OnTime Now + TimeValue("00:00:15"), "Capture"
End Sub

Excel 2013 record/refresh live stock data

1 Answers