Repeating Stock Ticker Tool From Access Table

Question

I'm attempting to merge two scripts to accomplish the following.

Run a http address to obtain stock data, based off a list of tickers on a table in my dB and save as a text file named as the ticker symbol.
Have the process loop until the list has been completely worked.

The following is the code I am working with (apologize in advance, it's probably horrible):

Private Sub GrabQuotes() 
  Dim oXMLHTTP
  Dim oStream
  Dim Symbol As String
  Dim i As Integer

  Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP.3.0")

  With Table("Ticker")
    For i = 1 To 55
        Symbol = .Fields(i, 4).Value
        oXMLHTTP.Open "GET", "http://finance.google.com/finance/info?client=ig&q=" & Symbol, False
        oXMLHTTP.Send

        If oXMLHTTP.Status = 200 Then
          Set oStream = CreateObject("ADODB.Stream")
          oStream.Open
          oStream.Type = 1
          oStream.Write oXMLHTTP.responseBody
          oStream.SaveToFile "\\HBFSBOS\APPS\TDID\StockQuotes\" & Symbol & ".txt"
          oStream.Close
        End If
    Next i
  End With
End Sub

No matter what changes I try to make to it, I keep getting errors. Can anyone spot what I am doing wrong?

Sub or Function not defined, with "Table("Ticker") highlighted. — SanLuka, Dec 10 '15 at 19:34
And additionally `Private Sub GrabQuotes()` highlighted as well. — SanLuka, Dec 10 '15 at 19:35

score 0 · Accepted Answer · answered Dec 24 '15 at 17:26

Decided to run Yahoo API in csv format. Much simpler and less data to have to screw with. Completed using the below code:

Option Compare Database

Public Function RunYahooAPI()

Dim chromePath As String

  chromePath = """C:\Program Files\Google\Chrome\Application\chrome.exe"""

 Shell (chromePath & " -url http://download.finance.yahoo.com/d/quotes.csv?s=CVX%2CXOM%2CHP%2CSLB%2CPBA&f=nsl1op&e=.csv")

End Function

Repeating Stock Ticker Tool From Access Table

1 Answers1