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]​

 

[igold]

Have you tried just returning the straight url as opposed to returning it to a QueryTable.

Also, why not get the free API key and use that instead of "demo"

 

[Shawn Yates]

Have you tried just returning the straight url as opposed to returning it to a QueryTable.

Also, why not get the free API key and use that instead of "demo"

I do have an API key and have used that in my actual code, but didn't really want to share it here. I will try your other suggestion tonight and see if I can get it to work.

 

[igold]

I thought that might be the case but wanted to double check.

I had to play with it a while before I was able to return the CSV to where I could manipulate it. I an not using "TIME_SERIES_DAILY" but rather "BATCH_STOCK_QUOTES".

In case you're interested:

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 i As Integer
    
    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=BATCH_STOCK_QUOTES&symbols=" & Symbols & "&apikey=YOURAPIHERE&datatype=csv"
    Dim http As New WinHttpRequest
    
    http.Open "GET", url, False
    http.Send
    
    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, 1).Value = Values(UBound(Values) - 3)
            w.Cells(i + 1, 2).Value = Values(UBound(Values) - 2)
            w.Cells(i + 1, 3).Value = Values(UBound(Values) - 1)
            w.Cells(i + 1, 4).Value = Values(UBound(Values))
             
        End If
    Next i
End Sub

Symbol list is column A

 

[Shawn Yates]

I thought that might be the case but wanted to double check.

I had to play with it a while before I was able to return the CSV to where I could manipulate it. I an not using "TIME_SERIES_DAILY" but rather "BATCH_STOCK_QUOTES".

In case you're interested:

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 i As Integer
    
    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=BATCH_STOCK_QUOTES&symbols=" & Symbols & "&apikey=YOURAPIHERE&datatype=csv"
    Dim http As New WinHttpRequest
    
    http.Open "GET", url, False
    http.Send
    
    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, 1).Value = Values(UBound(Values) - 3)
            w.Cells(i + 1, 2).Value = Values(UBound(Values) - 2)
            w.Cells(i + 1, 3).Value = Values(UBound(Values) - 1)
            w.Cells(i + 1, 4).Value = Values(UBound(Values))
             
        End If
    Next i
End Sub

Symbol list is column A

igold... that works great! I was able to do the same thing with the "TIME_SERIES_DAILY" data as well. I am surprised how fast it can bring this in line by line. Thanks for your help everyone!

 

[igold]

Happy to help. Thanks for the feedback!

 

[mrppp]

I thought that might be the case but wanted to double check.

I had to play with it a while before I was able to return the CSV to where I could manipulate it. I an not using "TIME_SERIES_DAILY" but rather "BATCH_STOCK_QUOTES".

In case you're interested:

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 i As Integer
    
    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=BATCH_STOCK_QUOTES&symbols=" & Symbols & "&apikey=YOURAPIHERE&datatype=csv"
    Dim http As New WinHttpRequest
    
    http.Open "GET", url, False
    http.Send
    
    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, 1).Value = Values(UBound(Values) - 3)
            w.Cells(i + 1, 2).Value = Values(UBound(Values) - 2)
            w.Cells(i + 1, 3).Value = Values(UBound(Values) - 1)
            w.Cells(i + 1, 4).Value = Values(UBound(Values))
             
        End If
    Next i
End Sub

Symbol list is column A

I receive a compiling error: User-defined type not defined.

Anyone which reference is missing?

Thanks!

 

[igold]

Perhaps:

"Microsoft WinHTTP Services, version 5.1"

 

[mrppp]

Perhaps:

"Microsoft WinHTTP Services, version 5.1"

Works, thanks.

If I want to switch to TIME_SERIES_DAILY_ADJUSTED, how do I best copy the data to individual work sheets which are overwritten every time, the data is requested?

 

[igold]

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.