Using Excel VBA to pull data from CSV file

Question

I'm a Yahoo Finance API refugee (they discontinued their API service) trying to switch to Alpha Vantage. I've modified the below code which I previously used for Yahoo Finance, but I'm getting a #VALUE error in Excel.

The URL below works by itself (it opens a CSV if you type it into your web browser), so I guess my real problem lies in extracting the correct data from the CSV into my Excel spreadsheet. Would anyone be able to help with this?

I'm trying to extract from the CSV the data in row 2, column 5 (the last closing price). Many thanks in advance!

Function StockClose(Ticker As String)

Dim URL As String, CSV As String, apikey As String, SCRows() As String, SCColumns() As String, pxClose As Double

apikey = "*censored*"

URL = "https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=" & Ticker & "&outputsize=full&" & apikey & "&datatype=csv"

Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
    xmlhttp.Open "GET", URL, False
    xmlhttp.Send
    CSV = xmlhttp.responseText

    'split the CSV into rows
    SCRows() = Split(CSV, Chr(10))
    'split the relevant row into columns. 0 means 1st row, starting at index 0
    SCColumns() = Split(SCRows(1), ",")
    '6 means: 5th column; starting at index 0 - price close is in the 5th column
    pxClose = SCColumns(6)

    StockClose = pxClose

Set http = Nothing

End Function

Sample of the data returned if I extract json instead of csv:

{ "Meta Data": { "1. Information": "Daily Prices (open, high, low, close) and Volumes", "2. Symbol": "SGD=X", "3. Last Refreshed": "2017-11-10", "4. Output Size": "Full size", "5. Time Zone": "US/Eastern" }, "Time Series (Daily)": { "2017-11-13": { "1. open": "1.3588", "2. high": "1.3612", "3. low": "1.3581", "4. close": "1.3587", "5. volume": "0" }, "2017-11-10": { "1. open": "1.3588", "2. high": "1.3612", "3. low": "1.3581", "4. close": "1.3587", "5. volume": "0" },

Check [this answer](https://stackoverflow.com/a/43969543/2165759), it might help you to manage with Yahoo Finance API. — omegastripes, Nov 11 '17 at 18:35

score 2 · Accepted Answer · answered Nov 11 '17 at 16:44

2

The "CSV" option from the website is a downloadable file, not a text document to parse like this. A simpler solution is to use the site's JSON option instead, which is easily loaded into a string.

Since you only need one value, it's easiest to just search for the string "Close", and return the value after it.

Here's a quick solution that you could adapt as needed:

Option Explicit

Function StockClose(Ticker As String) As Double

    Dim URL As String, json As String, apiKey As String, xmlHTTP As Object
    Dim posStart As Integer, posEnd As Integer, strClose As String

    apiKey = "demo"
    URL = "https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=" & Ticker & "&outputsize=full&apikey=" & apikey 

    Set xmlHTTP = CreateObject("MSXML2.XMLHTTP")
    xmlHTTP.Open "GET", URL, False
    xmlHTTP.Send
    json = xmlHTTP.responseText
    Set xmlHTTP = Nothing

    posStart = InStr(json, "4. close") + 12
    posEnd = InStr(posStart, json, """") - 1
    strClose = Mid(json, posStart, posEnd - posStart)

    StockClose = Val(strClose)

End Function

answered Nov 11 '17 at 16:44

ashleedawg

20,365
9
72
105

Thanks Ashlee, I tried the above code but the function always returns 0. Do you think it might be because there are multiple "4. close" in the json? Extracted a sample below: "Time Series (Daily)": { "2017-11-13": { "1. open": "1.3588", "2. high": "1.3612", "3. low": "1.3581", "4. close": "1.3587", "5. volume": "0" }, "2017-11-10": { "1. open": "1.3588", "2. high": "1.3612", "3. low": "1.3581", "4. close": "1.3587", "5. volume": "0" – volvader Nov 12 '17 at 11:54
hmm, it shouldn't matter. It's looking for the 1st appearance. Upper/lower case may or may not matter. Is the case identical in your file as in the code: `4. close`? – ashleedawg Nov 12 '17 at 11:56
what's the ticker symbol you're checking? – ashleedawg Nov 12 '17 at 11:56
With the JSON you pasted above, the function returns `1.358` for me. Depending on your database settings, perhaps it's comparing the strings differently, which should be easily fixed by changing two lines: `posStart = InStr(1, json, "4. Close", vbTextCompare) + 12` ...and... `posEnd = InStr(posStart, json, """", vbTextCompare) - 1` – ashleedawg Nov 12 '17 at 12:05
in the example above, the ticker is **SGD=X**. hmm yes `4. close` matches exactly. – volvader Nov 12 '17 at 12:07
The code isn't using an API key, just the demo, which apparently works for symbol **MSFT** but not **SGD=X**. Replace "demo" with your API Key (or get a new one, for free) . The json is returning: `"Information": "The **demo** API key is for demo purposes only. Please claim your free API key at (https://www.alphavantage.co/support/#api-key) to explore our full API offerings. It takes fewer than 20 seconds, and we are committed to making it free forever."` – ashleedawg Nov 12 '17 at 12:15
ah, i spotted an error in my code, my bad. it works perfectly now, many thanks ashlee! – volvader Nov 12 '17 at 12:18
...also, to help with debugging in a case like this, you could add a line after `Set xmlHTTP = Nothing` which says `Debug.Print json'. This will print the JSON text to the Immediate Window which you can see by pushing `CTRL+G` from the VB Editor. Also, look into how to use breakpoints for running code one line at a time, to find where errors might lie... (and you're welcome, I'm glad it worked for you. This is kind of a dirty-but-effective method for pulling bits of text out of any text based file, ie., html, xml, txt, js, etc. I use it to pull numbers off of my Utility Bill's sites.) – ashleedawg Nov 12 '17 at 12:21

score -1 · Answer 2 · answered Nov 22 '17 at 03:02

It was a sad day when Yahoo ended their API service. Nevertheless, there are MANY alternatives. You can use Python to get time series data for stocks. You can use R as well. I'll leave it to you to figure out how to utilize these technologies, because you are an Excel-VBA user and I don't want to force those options on you if you don't wan them. As an alternative, consider one of these two options, listed below.

http://finance.jasonstrimpel.com/bulk-stock-download/

http://investexcel.net/multiple-stock-quote-downloader-for-excel/

For the second URL, click the link titled 'Get Excel Spreadsheet to Download Bulk Historical Stock Data from Google Finance' and you can download a tool that should do everything you want.

Using Excel VBA to pull data from CSV file

2 Answers2