Since 2018, Excel has offered the ability to automatically integrate stock prices into Excel. This allows you to manage your stock portfolio in Excel in just a few steps.

In addition to stock prices, you also have access to additional information such as market capitalization, employee numbers, or P/E ratios.

In this guide, we show you how to import price data into Excel and use Excel as a portfolio dashboard.

At the end, it looks like this:

You can download the example template for free here:

Portfolio Management in Excel with Automatically Updated Stock Prices

How to Import Stock Prices into Excel

When importing stock prices into Excel, you can basically distinguish between current stock prices and historical stock prices.

Integrating Current Stock Prices (and More Information)

1. Step 1: Enter the company name in an empty cell for which you want to display stock prices. Alternatively to the company name, you can also use the name of a fund or a ticker symbol.
2. Step 2: Click on the Data tab and then on the Stocks symbol. If the Stocks symbol is not visible, you can scroll to the Stocks symbol using the arrow on the right side.
3. Step 3 (optional): Excel now tries to find the corresponding company. It often happens that you need to help Excel select the exact security and the respective exchange. In these cases, a sidebar appears with the found companies/exchanges. Select the appropriate company.
4. Step 4: The cell now shows the official name of the company and a stock symbol appears. Hover over the cell. A small list icon appears. Clicking on this icon opens a list of available data. Click on Price to display the current stock price.

That's all there is to it. The result should look something like this:

You can refresh the price information by clicking "Refresh All".

Retrieving Historical Stock Prices in Excel

In addition to current company information, Excel can also retrieve historical price data. For this, you need the Microsoft 365 subscription.

Retrieving historical prices works via the following function:

=STOCKHISTORY([Stock], [Start_Date], [End_Date], [Interval], [Headers], [Property0], [Property1], [Property2], [Property3], [Property4], [Property5])

Historical price data offers a lot of flexibility. In our template, for example, we've built in the ability to display a price chart for each entered security:

Consider Currencies and Convert if Necessary

For many securities, Excel offers a selection of different exchanges where the security is traded. To keep track, we recommend selecting the exchanges so that all securities are displayed in the same currency.

Alternatively, you can also import current and historical exchange rates of different currencies and adjust the security prices yourself.

Creating a Stock Portfolio Dashboard

With the ability to import current price data as well as historical price data, Excel provides a good foundation for creating a stock dashboard. The limit here is basically only your own imagination and your own Excel skills.

In our free template, we calculate, for example, the current overall portfolio performance, as well as the performance per security. Using a doughnut or pie chart, you can also graphically display the asset allocation.

New to Parqet?

At Parqet, we work every day to remove barriers to wealth building. We build tools that allow you to keep track of your portfolio effortlessly, whether at home or on the go.

See how Parqet can simplify your wealth planning, or create your free portfolio in seconds.