Getting Daily Stock Data from Alpha Vantage API

[Shawn Yates]

I can't seem to figure out if there is an issue with my code or if this just isn't possible. If you go to the following url it will download a csv file containing stock info.

https://www.alphavantage.co/query?f...ADJUSTED&symbol=MSFT&apikey=demo&datatype=csv

Now if I use my code below it gives me an error saying

"Run-time erroro '1004':

Unable to open
The Internet site cannot return th... = False End With End Sub [/CODE]​

 

[mrppp]

You're welcome.

I am not 100% sure I know what you're asking but at the code is working on the activesheet. If you want to switch to another sheet I would do it at the top before the code runs.

Basically, I want to download the full historical for a set of stocks and save the data for each ticker in a different worksheet. If the sheet already exists, the data should be overwritten (as updating won't work since we are not using a web query).

Sub test()    
    Application.ScreenUpdating = False
    Dim SymArr
    Dim wsQ As Worksheet: Set wsQ = Worksheets("Sheet1")
    wsQ.Select
    Dim w As Worksheet: Set w = ActiveSheet
    Dim last As Integer: last = w.Range("A3").End(xlDown).Row
    Dim Symbols As String
    Dim API As String
    Dim i As Integer
    
    API = "[ENTER API KEY]"
    
    SymArr = Range("A2:A" & last)
    If last = 1 Then Exit Sub
    For i = 1 To UBound(SymArr)
        Symbols = Symbols & SymArr(i, 1) & ","
    Next i
    Symbols = Left(Symbols, Len(Symbols) - 1)
    Dim url As String: url = "https://www.alphavantage.co/query?function=TIME_SERIES_DAILY_ADJUSTED&symbol=" & Symbols & "&apikey=" & API & "&datatype=csv"
    Dim http As New WinHttpRequest
      
    http.Open "GET", url, False
    http.Send

I assume the below is irrelevant for my task and needs to be altered. I am not sure how I can pick up loop

    Dim resp As String: resp = http.ResponseText
    Dim Lines As Variant: Lines = Split(resp, vbNewLine)
    Dim sLine As String
    Dim Values As Variant
    
    For i = 0 To UBound(Lines)
        sLine = Lines(i)
        On Error Resume Next
        If InStr(sLine, ",") > 0 Then
            Values = Split(sLine, ",")
            w.Cells(i + 1, 3).Value = Values(UBound(Values) - 3)
            w.Cells(i + 1, 4).Value = Values(UBound(Values) - 2)
            w.Cells(i + 1, 5).Value = Values(UBound(Values) - 1)
            w.Cells(i + 1, 6).Value = Values(UBound(Values))
             
        End If
    Next i
    
End Sub

Thanks!

 

[igold]

I am a little confused. The part of the code that you have deemed irrelevant is the part that writes the response to your sheet.

Did you run the code on a blank sheet putting a few stock symbols in column A to see what happens when the code runs...