Direct web query of stock quotes in Excel
The option I want to explain now actually always works. It is important to find a suitable website where the data is also stored in tabular form and where the data retrieval is also approved. It’s not like that everywhere, as I found out.
In my example, I want to retrieve the data from the Dax Index in real time. Of course you can also select your own tables on the page.
Now it continues as follows:
- First call up the URL: https://www.ariva.de/dax-index/realtime-kurse
- Copy the URL from your browser’s address bar
- Open Excel and in the “Data” tab on the left select “Data from Web“
- Enter the URL in the dialog window and confirm with “OK“
Depending on how busy the server is or how fast your internet connection is, it may take a minute or two for the tables to be retrieved from the website.
In the lower part of the window you will see the “Load” drop-down button. Here you have different options how you want to process the data retrieval. Actually, I had initially tended to Pivot Table, but the disadvantage here was that it was quite confusing, because each column contained sub-items (also advertisements). Then you would have had to try everything behind what is now hidden. Because the table names are not clearly displayed, but only with: “Table 0, Table 1, etc.”
That’s why I recommend just clicking on “Load“. You then have a spreadsheet that still contains some useless information, but it’s much easier to clean up. You can always convert this later into a pivot table and, if necessary, a pivot chart and sort the data as you like.
So click on the spreadsheets that appear, preview where the data you need is included, and then click Load. The stock quotes spreadsheet will then be imported into Excel as a smart spreadsheet.
see fig. (click to enlarge)