VBA Function: Yahoo Finance UDF

[leeboy168]

Hi guys,

For those familiar with Yahoo Finance and it's query language this is one for you. I have a function which is supposed to pull the historical stock price of a certain ticker symbol at a certain date, however when attempting to use the UDF it's coming up with the #VALUE error. I can't for the life of me figure out what's wrong...any ideas?

Thanks in advance for your help!

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

 

[leeboy168]

bump!