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

 

[John_w]

I don't know what you mean - try illustrating with screenshots.

 

[BascherPA]

At the moment the selected prices will be inserted into e.g. A2:B6.
Would it be possible to insert the selected price to the end of the already existing table in the spreadsheet?

I hope this illustrated what I mean.

 

[John_w]

Replace the 2 lines you posted with:

                With symbolSheet
                    .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(5, 2).Value = tempSheet.Range("A2:B6").Value
                End With

 

[BascherPA]

Hi all,

Would it be possible to also fetch the data Open, High, Low and Close and put them into separate columns?
Currently only the close price will be chosen.

Thank you in advance for your great support.

Best regards
Benjamin

 

[John_w]

Are you talking about a different time series now, since the current one returns 2 columns?

For 5 columns just change the Resize(number of rows, number of columns) to Resize(5, 5) and the Range to the same size, i.e. Range("A2:E6") in my last code snippet.

 

[BascherPA]

Hi John,

Unfortunately the wrong data were chosen and the output looks as follows:

Date	Open	High	Low	Close
25.06.2020​	1091300​	10000​	#NV​	#NV​
24.06.2020​	1095000​	10000​	#NV​	#NV​
23.06.2020​	1106100​	10000​	#NV​	#NV​
22.06.2020​	1069000​	10000​	#NV​	#NV​
19.06.2020​	1077700​	10000​	#NV​	#NV​
18.06.2020​	1075800​	10000​	#NV​	#NV​

What do I have to adjust to chose the data open, high, low and close from the temporary sheet?
The standard view is as follows:

​

​

​

​

​

​

​

Date	Open	High	Low	Close	Adj Close	Volume
25.06.2019​	99.720.001​	99.720.001​	96.989.998​	97.129.997​	93.646.370​	5339400​
26.06.2019​	97.610.001​	100.379.997​	97.139.999​	99.970.001​	96.384.521​	4624000​
27.06.2019​	100.339.996​	102.089.996​	100.169.998​	101.699.997​	98.052.467​	2613600​
28.06.2019​	102.239.998​	103.470.001​	102.239.998​	103.269.997​	99.566.154​	3130400​
01.07.2019​	104.580.002​	105.120.003​	102.379.997​	102.970.001​	99.276.917​	2627600​

In the current code the date and adjusted close was chosen.

BR
Benjamin

 

[John_w]

Replace the With QT .... End With block with:

        With QT
            .Name = "web_query"
            .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
            If copyAllRows Then
                symbolSheet.Range("A2:E2").Resize(.ResultRange.Rows.Count - 1).Value = .ResultRange.Offset(1).Value 'copy all rows, 5 columns
            Else
                'Copy top 5 rows and first 5 columns (timestamp, open, high, low, close) of imported data to bottom of symbol's data sheet
                With symbolSheet
                    .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(5, 5).Value = tempSheet.Range("A2:E6").Value
                End With
            End If
            .Delete
        End With

The main change is the removal of the .TextFileColumnDataTypes = Array(3, 9, 9, 9, 9, 1, 9, 9) which was in your original code.

 

[BascherPA]

Hello all,

I would like to use this macro which was created by support of this group to download historical data for futures from yahoo finance.
This macro works great for stock e.g. ticker APPL but not for futures where the ticker should be something like GC=F.
Doese anybody have an idea how to define the symbols/ticker for futures for yahoo finance in case of this macro?
If I tipe in GC=F then the following error occurs:

Thank you in advance for your support.

Best regards
Benjamin

 

[John_w]

I would like to use this macro which was created by support of this group to download historical data for futures from yahoo finance.
This macro works great for stock e.g. ticker APPL but not for futures where the ticker should be something like GC=F.

Please start a new thread because this thread is for www.alphavantage.co, not Yahoo finance.