Download multiple end of day data into Excel

BascherPA

New Member
Joined
May 3, 2020
Messages
23
Office Version
  1. 2010
Platform
  1. Windows
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.

VBA Code:
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

New Member
Joined
May 3, 2020
Messages
23
Office Version
  1. 2010
Platform
  1. Windows
John you are absolutely correct. Thank you so much for you support.
Great job.
Wish you all the best.

Best regards
Benjamin
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

BascherPA

New Member
Joined
May 3, 2020
Messages
23
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,751
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.

VBA Code:
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

New Member
Joined
May 3, 2020
Messages
23
Office Version
  1. 2010
Platform
  1. Windows
John, that works great. Fantastic job. (y)

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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,751

ADVERTISEMENT

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:
VBA Code:
                symbolSheet.Rows(2).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                symbolSheet.Range("A2:B2").Value = tempSheet.Range("A2:B2").Value
with:
VBA Code:
                symbolSheet.Rows("2:6").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                symbolSheet.Range("A2:B6").Value = tempSheet.Range("A2:B6").Value
 

BascherPA

New Member
Joined
May 3, 2020
Messages
23
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,751

ADVERTISEMENT

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

New Member
Joined
May 3, 2020
Messages
23
Office Version
  1. 2010
Platform
  1. Windows
Hi John,
I always have the same issue when using the code.
1591531727213.png

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

Do you know what is wrong here?

Best regards
Benjamin
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,751
The error means that NextSymbolCell is not defined (doesn't have a value), which means you haven't run Batch_Import_All_Symbols.
 

BascherPA

New Member
Joined
May 3, 2020
Messages
23
Office Version
  1. 2010
Platform
  1. Windows
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?

VBA Code:
   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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,573
Messages
5,637,142
Members
416,959
Latest member
Mohzein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top