Auto Refresh on Open?

[thompstc]

Hi everyone. VBA nincompoop here. I'm using some code I found online (not mine - all credit to the proper person) that fetched stock quotes based on a ticker symbol. Good enough, but it seems as though they don't refresh unless i click on the cell and hit enter. too manual. any way somebody can alter the code to auto refresh on open everytime? here's the code:

Function StockQuote(strTicker As String, Optional dtDate As Variant)
' Date is optional - if omitted, use today. If value is not a date, throw error.
If IsMissing(dtDate) Then
dtDate = Date
Else
If Not (IsDate(dtDate)) Then
StockQuote = CVErr(xlErrNum)
End If
End If


Dim dtPrevDate As Date
Dim strURL As String, strCSV As String, strRows() As String, strColumns() As String
Dim dbClose As Double


dtPrevDate = dtDate - 7


' Compile the request URL with start date and end date
strURL = "http://ichart.finance.yahoo.com/table.csv?s=" & strTicker & _
"&a=" & Month(dtPrevDate) - 1 & _
"&b=" & Day(dtPrevDate) & _
"&c=" & Year(dtPrevDate) & _
"&d=" & Month(dtDate) - 1 & _
"&e=" & Day(dtDate) & _
"&f=" & Year(dtDate) & _
"&g=d&ignore=.csv"


' Debug.Print strURL


Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", strURL, False
http.Send
strCSV = http.responseText


' Debug.Print strCSV


' The most recent information is in row 2, just below the table headings.
' The price close is the 5th entry
strRows() = Split(strCSV, Chr(10)) ' split the CSV into rows
strColumns = Split(strRows(1), ",") ' split the relevant row into columns. 1 means 2nd row, starting at index 0
dbClose = strColumns(4) ' 4 means: 5th position, starting at index 0


' Debug.Print vbLf
' Debug.Print strRows(1)
' Debug.Print "dbClose: " & dbClose


StockQuote = dbClose


Set http = Nothing


End Function

 

[Carpaccio]

Hi thompstc
you can call the function from a sub that runs when the workbook opens:

• Open an excel workbook
• Press Alt+F11 to open VBA Editor
• Double click on ThisWorkbook from Project Explorer
• Copy the above code and Paste in the code window
• Save the file as macro enabled workbook
• Open the workbook to test it, it will Run a Macro Automatically. You should see a message box as shown above

in my example, I call the function and pass "mytick" for strTicker and "1/7/2017" for dtDate

Private Sub Workbook_Open()
    StockQuote "mytick", "1/7/2017"
End Sub

HTH

Regards
Alan