VBA code leads to value in cell but works in immediate box

Question

I have written a getprice function for retrieving stock tickers from yahoo and bitcoin prices from an online api. However the code for bitcoin results in an #value! in my excel cell. But it performs fine when I run the function from the immediate box in VB.

This is my code: 

Function GetPrice(strTicker As String, Optional dtDate As Variant)

  Debug.Print "Getting Price..."

  ' 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
      GetPrice = CVErr(xlErrNum)
      Debug.Print "Date problem!"
      Exit Function
    End If
  End If

  ' Define variables
  Dim dtPrevDate As Date
  Dim strURL As String, strCSV As String, strDate As String, strRows() As String, strColumns() As String
  Dim priceArray() As Variant
  Dim wb As Workbook
  Dim dbClose As Double
  dbClose = 1 ' default for if price not found

  ' for stock tickers look at a week's worth of data in case date is weekend
  dtPrevDate = dtDate - 7

  ' Treat bitcoin separately and compile CSV with all BTC data
  If strTicker = "BTCUSD" Then
     ' Dummy placeholder URL
     strURL = "[BITCOIN_CSV_API_URL_REMOVED]"
     priceArray = CsvToArray(strURL)

     strDate = CStr(dtDate)
     Debug.Print "Date: "; strDate

     For i = LBound(priceArray) To UBound(priceArray)
         If CStr(priceArray(i, 1)) = strDate Then
             dbClose = CDbl(priceArray(i, 4))
             Exit For
         Else
             dbClose = 1
         End If
     Next i

  ' For all other tickers
  Else
    Debug.Print "stock ticker:"; strTicker
    strURL = "[YAHOO_FINANCE_CSV_URL_REMOVED]"

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

    strRows() = Split(strCSV, Chr(10))
    strColumns = Split(strRows(1), ",")
    dbClose = strColumns(4)
  End If

  If dbClose = 1 Then
    GetPrice = "Not Found"
    Debug.Print "GetPrice"; GetPrice
  Else
    GetPrice = dbClose
    Debug.Print "Price: "; GetPrice
  End If

  Set http = Nothing

End Function

Function CsvToArray(filepath As String) As Variant
  Dim wb As Workbook
  Dim array1() As Variant

  Application.ScreenUpdating = False
  Set wb = Workbooks.Open(filepath)

  array1 = wb.Sheets(1).Range("A1").CurrentRegion.Value
  wb.Close False

  CsvToArray = array1
  Application.ScreenUpdating = True
End Function

Answer 1

I don't think you can access the bitcoin url the way that you're trying to do so. Excel can't actually trigger the file download & and the file path that you're trying to use for the CsvToArray method doesn't make sense.

try something like this:

Function GetPrice(strTicker As String, Optional dtDate As Variant)

Debug.Print "Getting Price..."

' 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
      GetPrice = CVErr(xlErrNum)
      Debug.Print "Date problem!"
   End If
End If

' Define variables
Dim dtPrevDate As Date
Dim strURL As String, strCSV As String, strDate As String, strRows() As String, strColumns() As String
Dim priceArray() As Variant
Dim wb As Workbook
Dim dbClose As Double
Dim desiredRow, desiredCol As Long
dbClose = 1 ' default for if price not found

' for stock tickers look at a weeks worth of data in case date is weekend
dtPrevDate = dtDate - 7

' Treat bitcoin separately and compile CSV with all BTC data
If strTicker

Answer 2

Function GetPrice(strTicker As String, Optional dtDate As Variant)

Debug.Print "Getting Price..."

' 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
    GetPrice = CVErr(xlErrNum)
    Debug.Print "Date problem!"
End If
End If

' Define variables
Dim dtPrevDate As Date
Dim strURL As String, strCSV As String, strDate As String, strRows() As String, strColumns() As String
Dim priceArray() As Variant
Dim wb As Workbook
Dim dbClose As Double
dbClose = 1 ' default for if price not found

' for stock tickers look at a weeks worth of data in case date is weekend
dtPrevDate = dtDate - 7

' Treat bitcoin separately and compile CSV with all BTC data
If strTicker = "BTCUSD" Then
    ' go to the URL
    strURL = "https://api.bitcoinaverage.com/history/USD/per_day_all_time_history.csv"
    'priceArray = CsvToArray(strURL) 'convert to array

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

    'split csv into rows
    strRows() = Split(strCSV, Chr(10))

    ' Bitcoin date search
    numDate = CDate(dtDate)
    Debug.Print "Date: "; CStr(dtDate)

    'compare dates in strCSV to dtDate
    For i = 1 To UBound(strRows())
        rowInfo = Split(strRows(i), ",") 'divide the rows by commas
        rowDate = Split(rowInfo(0), " ") 'look at the date/time and take the date only
        rowDate = CDate(rowDate(0))      'convert the date from string to CDate
        If rowDate >= numDate Then       'Make the comparison
            dbClose = CDbl(rowInfo(3))   'set the price as daily avg
            Exit For                     'exit for loop
        Else
            dbClose = 1
        End If
    Next i


' For all other tickers
' Compile the request URL with start date and end date
Else
    Debug.Print "stock ticker:"; strTicker
    strURL = "http://ichart.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"

    ' Declare an object as the http data
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", strURL, False
    http.Send
    strCSV = http.responseText

    ' 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

End If

If dbClose = 1 Then
    GetPrice = 1
    Debug.Print "GetPrice"; GetPrice
Else
    GetPrice = dbClose
    Debug.Print "Price: "; GetPrice
End If

Set http = Nothing

End Function