title: Using Excel VBA to pull data from CSV file
body: <p>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.</p>
  
  <p>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?</p>
  
  <p>I'm trying to extract from the CSV the data in row 2, column 5 (the last closing price). Many thanks in advance!</p>
  
  <pre><code>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&amp;symbol=" &amp; Ticker &amp; "&amp;outputsize=full&amp;" &amp; apikey &amp; "&amp;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
  </code></pre>
  
  <p>Sample of the data returned if I extract json instead of csv:</p>
  
  <blockquote>
    <p>{
        "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"
            },</p>
  </blockquote>