0

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

Automate This
  • 30,726
  • 11
  • 60
  • 82
Chris
  • 39
  • 2
  • 4

1 Answers1

1

Two things:

  1. Declare i as a public (Global) variable so it will hold its values between iterations.

  2. 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
feetwet
  • 3,248
  • 7
  • 46
  • 84
JNevill
  • 46,980
  • 4
  • 38
  • 63
  • Thank you for responding to my question. When I run the program it only runs once without repeating. – Chris Oct 01 '14 at 02:15
  • Everything looks good to me as is. I've tested the same code, but replace the Sheets("recorddata").cells(1, i) line with something else, and did a 1 second interval and had success. You may want to try, for debugging purposes, making a second Subroutine called Capture2. Have Capture() call Capture2 in your .OnTime line and have Capture2 call Capture on it's OnTime() line. You can have capture 2 do something like `msgbox("Capture2")` or something to confirm it's being kicked off. – JNevill Oct 01 '14 at 12:07