Download multiple end of day data into Excel

[BascherPA]

Hello all,

I am glad to be here and post my first thread.

I want to load historical data from yahoo finance into excel via VBA.
Because this is only possible for one symbol in my code I want to enlarge this code to determine a list of symbols/stocks and download the end of day/week adjusted close prices into a cell.
Best solution would be if the downloaded price could be placed besides the cell of ticker form the list.
Unfortunately I am a novice in excel and do not know how to implement an array for such a list so I would be very pleased if somebody could tell me the correct code for this aim.

Sub Daten_aus_excel()


Range("A9:G5000").ClearContents

Dim ticker As String
ticker = Range("B4")


    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;https://www.alphavantage.co/query?function=TIME_SERIES_MONTHLY_ADJUSTED&symbol=" & ticker & "&apikey=demo&datatype=csv" _
        , Destination:=Range("$A$9"))
        .Name = _
        "query?function=TIME_SERIES_MONTHLY_ADJUSTED&symbol=" & ticker & "&apikey=demo&datatype=csv_1"
        
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(3, 9, 9, 9, 9, 1, 9, 9)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Thank you in advance for your support.

Best regards
Benjamin

 

[BascherPA]

John you are absolutely correct. Thank you so much for you support.
Great job.
Wish you all the best.

Best regards
Benjamin

 

[BascherPA]

Hi John,
Would it be possible to adjust the code in the way that after fetching 2 symbols there have to be a break for one minute and after termination of this minute or two the next two symbols can be fetched until complete list in column A is worked off?

Thank you in advance for your support.

Best regards
Benjamin

 

[John_w]

This code implements a batch requester. The number of symbols requested in each batch and the time interval between batches can be configured in the Const lines.

This entire code replaces the Const APIkey = "demo" line in the previous code. It calls the same basic query routine, Import_Symbol_Data.

Const APIkey = "demo"

Public Const cBatchImportInterval As String = "00:01:00"
Public Const cNumSymbolsPerBatch As Long = 2
Public Const cBatchImportProcedure As String = "Batch_Import_Next_Symbols"

Public NextImportTime As Date
Public NextSymbolCell As Range


Public Sub Batch_Import_All_Symbols()

    Set NextSymbolCell = ThisWorkbook.Worksheets("Symbols").Range("A2")
    Batch_Import_Next_Symbols
    
End Sub


Private Sub Batch_Import_Next_Symbols()

    Dim tempSheet As Worksheet
    Dim n As Long
    
    With ThisWorkbook
        Set tempSheet = .Worksheets.Add(after:=.Worksheets(.Worksheets.Count))
    End With
    
    n = 0
    While Not IsEmpty(NextSymbolCell.Value) And n < cNumSymbolsPerBatch
        Import_Symbol_Data NextSymbolCell.Value, tempSheet
        Set NextSymbolCell = NextSymbolCell.Offset(1)
        n = n + 1
    Wend
    
    Application.DisplayAlerts = False
    tempSheet.Delete
    Application.DisplayAlerts = True
    
    If Not IsEmpty(NextSymbolCell.Value) Then
        Start_Timer
    Else
        Stop_Timer
        MsgBox "Batch run finished - all symbols imported"
    End If
    
End Sub


Private Sub Start_Timer()

    NextImportTime = Now + TimeValue(cBatchImportInterval)
    Application.OnTime EarliestTime:=NextImportTime, Procedure:=cBatchImportProcedure, Schedule:=True
    
End Sub


Private Sub Stop_Timer()

    On Error Resume Next
    Application.OnTime EarliestTime:=NextImportTime, Procedure:=cBatchImportProcedure, Schedule:=False
    On Error GoTo 0
    
End Sub

Run the Batch_Import_All_Symbols macro to start importing all the symbols, starting at A2 in the "Symbols" sheet.

 

[BascherPA]

John, that works great. Fantastic job.

Because the data fetching should only be done once a week (on saturday or sunday) I would to ask if there is a possibility to import the last 5 working days (Monday-Friday) instead of only the last and insert them into the appropriate data sheet?

Best regards
Benjamin

 

[John_w]

import the last 5 working days (Monday-Friday)

That doesn't make sense for the TIME_SERIES_MONTHLY_ADJUSTED data, which the macro is importing, because only the first row of web data relates to the current month - the data for the latest day of the current month.

If you're importing some other time series then to import the newest 5 rows of web data (assuming they are rows 2:6) replace:

                symbolSheet.Rows(2).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                symbolSheet.Range("A2:B2").Value = tempSheet.Range("A2:B2").Value

with:

                symbolSheet.Rows("2:6").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                symbolSheet.Range("A2:B6").Value = tempSheet.Range("A2:B6").Value

 

[BascherPA]

Hi John,
In the meanwhile I am using TIME_SERIES_DAILY_ADJUSTED. Maybe it is possible to write the dates which are required in column B. E.g. the last 5 dates in B2:B6. This quantity of dates in this example 6 rows should be unserted into the appropriate sheet. This allows to be more flexible.
Is that possible?
Best regards
Benjamin

 

[John_w]

Well we could specify the required dates, but then the coding is more complex. It's easier for me to import the last 5 days of the daily data that you asked for.

In addition to my previous post, change TIME_SERIES_MONTHLY_ADJUSTED in the URL to TIME_SERIES_DAILY_ADJUSTED.

The web data is actually 3 columns, not 2, so if you want all 3 columns change A2:B6 in my previous code to A2:C6.

 

[BascherPA]

Hi John,
I always have the same issue when using the code.

Error 91. Object or With Block-variable not determined.

Do you know what is wrong here?

Best regards
Benjamin

 

[John_w]

The error means that NextSymbolCell is not defined (doesn't have a value), which means you haven't run Batch_Import_All_Symbols.

 

[BascherPA]

Hello John,
Would there also be a possibility to add the values which I choose manually with the following adjustment below the last row in the table?

   symbolSheet.Rows("2:6").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                        symbolSheet.Range("A2:B6").Value = tempSheet.Range("A2:B6").Value

Many thanks in advance for your support.

Best regards
Benjamin