Import Stock Quotes into Excel – Tutorial
You can find out the stock exchange prices from all possible sources on the Internet, and you can also display everything with extensive figures with historical data, diagrams and charts. However, it is also possible to design everything according to your own taste and to import the share prices directly into Excel and, of course, to have them updated continuously. In this short tutorial we want to look at two different methods on how to import the stock market prices of the most important commodities and currencies into Excel.
Import Stock Quotes into Excel – Tutorial
You can find out the stock exchange prices from all possible sources on the Internet, and you can also display everything with extensive figures with historical data, diagrams and charts. However, it is also possible to design everything according to your own taste and to import the share prices directly into Excel and, of course, to have them updated continuously. In this short tutorial we want to look at two different methods on how to import the stock market prices of the most important commodities and currencies into Excel.
Import stock quotes into Office 365
Import stock quotes into Office 365
The easiest way to get stock quotes is in Office 365. But you also need the subscription model of the Office Suite for this. Although Office 365 offers the full potential, it usually causes annual costs depending on the model. It’s not worth it for everyone, which is why the stand-alone versions (most recently Office 2021) continue to sell well. The method below only works with Office 365. Unfortunately, the “Data types” option in the “Data” tab is not included in the individual versions.
To import stock prices into Office 365, proceed as follows:
- Open Excel in Office 365.
- Select the “Data” tab in the menu bar.
- Then “Get Data” and then “From Stock Quotes“.
- Enter the symbol of the stock you want to get the price for or use the search function.
- Select the data you want and click “OK“.
- Excel creates a data connection to the stock prices and updates the data automatically.
Using Power Query in Excel:
- Open Excel in Office 365.
- Select the “Data” tab in the menu bar.
- Then “Get Data” and then “From More Sources” > “Online Services” > “Stock Prices“.
- Enter the symbol of the stock or search by name.
- Configure the options you want, then click Load.
- Excel loads the data into a table, which you can then edit further with a pivot table, for example.
Both methods use external data connections from the web to retrieve stock prices and plot them in Excel. You can configure the update frequency of the data connection to update prices regularly.
The easiest way to get stock quotes is in Office 365. But you also need the subscription model of the Office Suite for this. Although Office 365 offers the full potential, it usually causes annual costs depending on the model. It’s not worth it for everyone, which is why the stand-alone versions (most recently Office 2021) continue to sell well. The method below only works with Office 365. Unfortunately, the “Data types” option in the “Data” tab is not included in the individual versions.
To import stock prices into Office 365, proceed as follows:
- Open Excel in Office 365.
- Select the “Data” tab in the menu bar.
- Then “Get Data” and then “From Stock Quotes“.
- Enter the symbol of the stock you want to get the price for or use the search function.
- Select the data you want and click “OK“.
- Excel creates a data connection to the stock prices and updates the data automatically.
Using Power Query in Excel:
- Open Excel in Office 365.
- Select the “Data” tab in the menu bar.
- Then “Get Data” and then “From More Sources” > “Online Services” > “Stock Prices“.
- Enter the symbol of the stock or search by name.
- Configure the options you want, then click Load.
- Excel loads the data into a table, which you can then edit further with a pivot table, for example.
Both methods use external data connections from the web to retrieve stock prices and plot them in Excel. You can configure the update frequency of the data connection to update prices regularly.
Import stock quotes into Excel
Import stock quotes into Excel
The variant in Office 365 is the most convenient, but of course not the only one. So let’s look at the other ways we can get stock quotes imported into Excel.
Use of Excel add-ins
- Go to “Add-Ins” in the Developer tab
- Then click “Store” in the next dialog window
- Either use the search function or go to the “Financial Management” category
- If you haven’t activated the developer tools yet, you can find a tutorial for it here >>>
Here you will find a whole range of providers who make it fairly easy to call up the share prices. But as always in life, nothing is really for free, because you usually have to register (free of charge) with the providers. So once again you pay with your data and not with hard cash.
see fig. (click to enlarge)
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)
The variant in Office 365 is the most convenient, but of course not the only one. So let’s look at the other ways we can get stock quotes imported into Excel.
Use of Excel add-ins
- Go to “Add-Ins” in the Developer tab
- Then click “Store” in the next dialog window
- Either use the search function or go to the “Financial Management” category
- If you haven’t activated the developer tools yet, you can find a tutorial for it here >>>
Here you will find a whole range of providers who make it fairly easy to call up the share prices. But as always in life, nothing is really for free, because you usually have to register (free of charge) with the providers. So once again you pay with your data and not with hard cash.
see fig. (click to enlarge)
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)
Update stock quotes in Excel
Update stock quotes in Excel
And since we can’t really do much with a static list, of course, we now make sure that you always have the most up-to-date data possible. In order to accomplish this, Excel once again offers a wide variety of options that you can use. Either go to the “Data” tab and then under “Refresh data” to “Query properties” or like me, where the table connections are currently displayed anyway, via the table’s context menu (right mouse button).
Here I recommend activating the check mark for “Update when opening the file“. Then you have the current stock prices at hand every time you open the file. Optionally, you can also specify a fixed update rate in minutes. For example, if it is an Excel file that runs in the background all day anyway.
Once the table has been sorted and the useless information has been removed, the whole thing can be easily inserted into Excel in a wide variety of applications. It could then look like this:
see fig. (click to enlarge)
And since we can’t really do much with a static list, of course, we now make sure that you always have the most up-to-date data possible. In order to accomplish this, Excel once again offers a wide variety of options that you can use. Either go to the “Data” tab and then under “Refresh data” to “Query properties” or like me, where the table connections are currently displayed anyway, via the table’s context menu (right mouse button).
Here I recommend activating the check mark for “Update when opening the file“. Then you have the current stock prices at hand every time you open the file. Optionally, you can also specify a fixed update rate in minutes. For example, if it is an Excel file that runs in the background all day anyway.
Once the table has been sorted and the useless information has been removed, the whole thing can be easily inserted into Excel in a wide variety of applications. It could then look like this:
see fig. (click to enlarge)
About the Author:
About the Author:
Popular Posts:
With the webcam to your dream job: Preparation for the video job interview!
Streamline your video interview with our tips on technique, setting, dress, body language and preparation. It's not quite success from the sofa, but this is where you can prove your professionalism.
IT security: The deceptive appearance of digital security
In a digitized world, IT security is an important factor. But technology alone is not enough. A holistic strategy is needed that also takes into account the human component.
Import Stock Quotes into Excel – Tutorial
Importing stock quotes into Excel is not that difficult. And you can do a lot with it. We show you how to do it directly without Office 365.
Create Excel Budget Book – with Statistics – Tutorial
Create your own Excel budget book with a graphical dashboard, statistics, trends and data cut-off. A lot is possible with pivot tables and pivot charts.
Excel random number generator – With Analysis function
You can create random numbers in Excel using a function. But there are more possibilities with the analysis function in Excel.
Excel Database with Input Form and Search Function
So erstellen Sie eine Datenbank mit Eingabemaske und Suchfunktion OHNE VBA KENNTNISSE in Excel ganz einfach. Durch eine gut versteckte Funktion in Excel geht es recht einfach.
Spring Specials 2024: Word & Excel Templates
Popular Posts:
With the webcam to your dream job: Preparation for the video job interview!
Streamline your video interview with our tips on technique, setting, dress, body language and preparation. It's not quite success from the sofa, but this is where you can prove your professionalism.
IT security: The deceptive appearance of digital security
In a digitized world, IT security is an important factor. But technology alone is not enough. A holistic strategy is needed that also takes into account the human component.
Import Stock Quotes into Excel – Tutorial
Importing stock quotes into Excel is not that difficult. And you can do a lot with it. We show you how to do it directly without Office 365.
Create Excel Budget Book – with Statistics – Tutorial
Create your own Excel budget book with a graphical dashboard, statistics, trends and data cut-off. A lot is possible with pivot tables and pivot charts.
Excel random number generator – With Analysis function
You can create random numbers in Excel using a function. But there are more possibilities with the analysis function in Excel.
Excel Database with Input Form and Search Function
So erstellen Sie eine Datenbank mit Eingabemaske und Suchfunktion OHNE VBA KENNTNISSE in Excel ganz einfach. Durch eine gut versteckte Funktion in Excel geht es recht einfach.
Spring Specials 2024: Word & Excel Templates
