To monitor your stocks, you might want to track the stock portfolio. In Excel, it is easy to create a sheet that will help you track the sharemarket status and the condition of your stocks. In this article, we will go through a step-by-step guide on how to track a stock portfolio in Excel. We will learn how to retrieve current prices, compare them with purchase prices, identify returns, and create charts/graphs. We will also demonstrate how to refresh the data easily, so you don’t have to manually update the stock prices.
➤ Insert the stock tickers in a column, and go to Data > Stocks to convert them to stock data.
➤ From the Insert Data menu of those stocks, add the required columns like Ticker, P/E, and Price.
➤ Insert the Shares and Purchase Price manually.
➤ Calculate the Value by multiplying the price by the shares, and the Investment by multiplying the shares by the purchase price.
➤ Calculate the Returns by the following formula:
=(G2-H2)/H2
➤ Replace G2 with the value and H2 with the investment.
➤ Insert the Value and Returns graphs by selecting those columns separately with the Ticker column, and going to the Insert > Charts and choosing the desired chart.
In the tutorial below, we will learn each and every step you need to take to prepare a worksheet that will track the stock information for you. Therefore, open Microsoft Excel and follow the steps carefully to keep track of your stocks.
Steps to Track a Stock Portfolio in Excel
We have 10 stock tickers in our workbook that we will use to create the portfolio. We will use various Excel functions to retrieve the stock data and do the calculations. Make sure that you are logged in to Microsoft 365 before starting, as the stock-related functions won’t work without an online account. Let’s begin:
Step 1: Retrieve Stock Data
First, we need to retrieve data from the internet to work with it. Follow the steps below:
➤ Extend the table with the following columns:
Ticker
P/E
Price
Shares
Purchase Price
Value
Investment
Returns
➤ Select the stock tickers from the A2:A11 cells.
➤ Go to the Data tab from the ribbon.
➤ Select Stocks from the Data Types group.
➤ The stock names will be retrieved from the internet. Keep the A2:A11 cells selected, and click on the Insert Data icon to open a menu. Select “Ticker symbol” from the menu.
➤ Similarly, select P/E and Price afterwards from the menu to fill the C and D columns.
➤ Now, fill the Shares and Purchase Price columns using the number of shares you purchased and the price you paid for each share.
Step 2: Calculate the Prices and Returns
Now that we have all the data to work with, we can proceed with the calculation. Start with the procedure below:
➤ In the G2 cell, insert the following formula, then drag the small plus (+) sign on the bottom-right corner of the cell to the G11 cell to autofill the column.
=D2*E2
We are multiplying the prices of the shares by the number of shares we bought to get the value of the stock.
➤ In the H2 cell, enter the formula from below and autofill the column.
=E2*F2
We are calculating the investment we made in the stock by multiplying the share numbers by the purchase price.
➤ Insert the following formula in the I2 cell of the Returns column, and autofill till I11.
=(G2-H2)/H2
We are calculating the return percentage by subtracting the investment in H2 from the current value in G2, then dividing the result by the investment.
➤ These values are in decimal. To convert them to a percentage, select I2:I11 cells, and select the percentage icon (%) from the Number group in the Home tab.
Step 3: Prepare the Charts
The tables are ready, now we can proceed to prepare the charts for the portfolio.
➤ Select B1:B11, hold the Shift button on the keyboard, and select G1:G11.
➤ Go to the Insert tab, head to the Charts group, and select the first 2-D Column.
➤ Now the chart for values will show up on the worksheet.
➤ Select the B1:B11 cells and the I1:I11 cells, go to Insert > Charts, and select the first 2-D Line.
➤ This should create a graph like the following:
Step 4: Summarizing and Refreshing
These are some fancy tweaks in the workbook that we can do to make the sheet look better and give a better overview of the data. Follow the instructions below:
➤ Create a small table for Total Investment, Current Value, Total Gain/Loss, and Total Return.
➤ To calculate the Total Investment, use the following formula:
=SUM(H2:H11)
➤ Add the values in the current value cell with the following formula:
=SUM(G2:G11)
The values in the G column are added using the SUM function.
➤ Calculate the Total Gain/Loss using the formula from below:
=G29-G28
Here, G29 contains the current value, and G28 contains the total investment. We are subtracting the total investment from the current value to calculate the total gain/loss.
➤ Calculate the average return using the following formula:
=AVERAGE(I2:I11)
➤ Now, let’s add a refresh button to the sheet to refresh the data automatically.
➤ Go to the Insert tab, and head to Illustrations > Shapes > Rectangles and select the first one.
➤ Draw the shape on the worksheet.
➤ Write Refresh on the shape. Then, go to the Home tab, select the Refresh word, and change the alignment like the image below. Write 36 in the box to change the font size.
➤ Press Alt + F11 to open the Microsoft Visual Basic for Applications window, and go to Insert > Module to open the code editor.
➤ Now write the following code in the code editor:
Sub RefreshWorkbook()
ActiveWorkbook.RefreshAll
End Sub
➤ Go back to the worksheet, right-click on the “Refresh” rectangle, and select Assign Macro.
➤ Select RefreshWorkbook and click OK.
➤ Now, whenever you click on the Refresh rectangle, the stock data will be refreshed.
Frequently Asked Questions
Can you use Google Finance in Excel?
No. The GOOGLEFINANCE function is exclusive to Google Sheets. In Excel, there are other functions that you can use to work with stocks. You can also create custom functions to mimic the GOOGLEFINANCE function.
How to get stock history in Excel?
There is a function in Excel called STOCKHISTORY. The function can be used with the following parameters to get the stock history in Excel.
=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], [property2], [property3], [property4], [property5])
The first three parameters are self-explanatory. The interval can be 0 for daily, 1 for weekly, and 2 for monthly. The headers parameter is to ask Excel to add headers or not. 0 means no headers, 1 means show headers, and 2 means show instrument identifier and headers. The properties are for these columns: 0 = Date, 1 = Close, 2 = Open, 3 = High, 4 = Low, 5 = Volume.
What is the 7% rule in stocks?
The rule is that if the stock price drops 7% below the price you paid to purchase the stock, you should consider selling the stock before the price drops further. If you are a seasonal investor and don’t like to hold stock, this is a good way to limit your losses.
How to make a stock list in Excel?
Create a table with the columns stating the required information from each stock. Then, use the ticker symbols to create a list. Using the ticker symbols, insert data from Data >Stocks in Excel. If you need to update the data, click on the Refresh button of the data tab.
Can I use AI to predict the stock market?
Yes. Many stock market tools already have built-in AI tools to analyze the condition of the market and predict. However, you should not trust the AI blindly, as it can make mistakes, sometimes more than humans can.
Wrapping Up
In this article, we have learned how to track a stock portfolio in Excel. The workbook, including the macro, is available for free download. If you have any questions regarding the tutorial, leave them below, and we will get back to you with solutions. We look forward to sharing more with you in a future article.
