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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

BascherPA

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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,751
Replace the 2 lines you posted with:
VBA Code:
                With symbolSheet
                    .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(5, 2).Value = tempSheet.Range("A2:B6").Value
                End With
 

BascherPA

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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,751

ADVERTISEMENT

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

New Member
Joined
May 3, 2020
Messages
23
Office Version
  1. 2010
Platform
  1. Windows
Hi John,

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

DateOpenHighLowClose
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:


DateOpenHighLowCloseAdj CloseVolume
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,751

ADVERTISEMENT

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

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

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

1613849717966.png


1613849748902.png


Thank you in advance for your support.

Best regards
Benjamin
 

John_w

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

Watch MrExcel Video

Forum statistics

Threads
1,129,574
Messages
5,637,159
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