Der Blog für digitale Kompetenz

Der Blog für digitale Kompetenz

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.

aktienkurse in excel

Topic Overview

Anzeige

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.

aktienkurse in excel

Topic Overview

Anzeige

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)

Aktienkurse in Excel mit Add Ins
cshow
Ads

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)

Aktienkurse aus dem Web abrufen
Aktienkurse in Excel importieren Abb.1
Aktienkurse in Excel importieren Abb.2

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)

Aktienkurse in Excel mit Add Ins
cshow
Ads

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)

Aktienkurse aus dem Web abrufen
Aktienkurse in Excel importieren Abb.1
Aktienkurse in Excel importieren Abb.2

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)

Aktienkurse in Excel importieren Abb.3
Aktienkurse in Excel importieren Abb.4

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)

Aktienkurse in Excel importieren Abb.3
Aktienkurse in Excel importieren Abb.4

Search for:

About the Author:

Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

Search for:

About the Author:

Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

Popular Posts:

102, 2024

Integrate and use ChatGPT in Excel – is that possible?

February 1st, 2024|Categories: Artificial intelligence, ChatGPT, Microsoft Excel, Microsoft Office, Shorts & Tutorials|Tags: , , , |

ChatGPT is more than just a simple chatbot. Learn how it can revolutionize how you work with Excel by translating formulas, creating VBA macros, and even promising future integration with Office.

2008, 2023

Internet Addiction – A serious look at a growing problem

August 20th, 2023|Categories: Google, Homeoffice, Shorts & Tutorials|Tags: , , |

Internet addiction is just as serious an illness as alcohol or drug addiction. Just that this is a mental illness. In this article we want to go into the phenomenon in more detail and provide assistance.

607, 2023

Main keyboard shortcuts in Windows 10/11

July 6th, 2023|Categories: Homeoffice, Microsoft Office, Shorts & Tutorials, Windows 10/11/12|Tags: |

Entdecken Sie die wichtigsten Shortcuts in Windows 11, um Ihre Produktivität zu steigern. Von allgemeinen Befehlen bis hin zu spezifischen Fenster-Management- und System-Shortcuts, lernen Sie, wie Sie mit diesen Tastenkombinationen effizienter arbeiten können.

107, 2023

Encrypt USB stick – These options are available

July 1st, 2023|Categories: Shorts & Tutorials, Data Protection, Hardware, Homeoffice, Mac OS, Windows 10/11/12|Tags: , , |

Protecting sensitive data is essential. Encrypting USB sticks provides an extra layer of security. Whether it's built-in software, operating system features, third-party software, or hardware encryption, there are numerous options.

Offers 2024: Word & Excel Templates

Special Offers 2024: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones

Popular Posts:

102, 2024

Integrate and use ChatGPT in Excel – is that possible?

February 1st, 2024|Categories: Artificial intelligence, ChatGPT, Microsoft Excel, Microsoft Office, Shorts & Tutorials|Tags: , , , |

ChatGPT is more than just a simple chatbot. Learn how it can revolutionize how you work with Excel by translating formulas, creating VBA macros, and even promising future integration with Office.

2008, 2023

Internet Addiction – A serious look at a growing problem

August 20th, 2023|Categories: Google, Homeoffice, Shorts & Tutorials|Tags: , , |

Internet addiction is just as serious an illness as alcohol or drug addiction. Just that this is a mental illness. In this article we want to go into the phenomenon in more detail and provide assistance.

607, 2023

Main keyboard shortcuts in Windows 10/11

July 6th, 2023|Categories: Homeoffice, Microsoft Office, Shorts & Tutorials, Windows 10/11/12|Tags: |

Entdecken Sie die wichtigsten Shortcuts in Windows 11, um Ihre Produktivität zu steigern. Von allgemeinen Befehlen bis hin zu spezifischen Fenster-Management- und System-Shortcuts, lernen Sie, wie Sie mit diesen Tastenkombinationen effizienter arbeiten können.

107, 2023

Encrypt USB stick – These options are available

July 1st, 2023|Categories: Shorts & Tutorials, Data Protection, Hardware, Homeoffice, Mac OS, Windows 10/11/12|Tags: , , |

Protecting sensitive data is essential. Encrypting USB sticks provides an extra layer of security. Whether it's built-in software, operating system features, third-party software, or hardware encryption, there are numerous options.

Offers 2024: Word & Excel Templates

Special Offers 2024: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones

Title

Ads

Popular Posts:

Search by category:

Autumn Specials:

Anzeige
Go to Top