Many people invest in the Stock market to profit from their idle money. Almost every country has a stock exchange office in their big cities for the convenience of the shareholders to check the stock price, purchasing, and selling. However, you can inspect your stock’s current situation from your home just by using Microsoft Excel. We will demonstrate to you how to track stocks in Excel in this context. If you are curious to know how to track stocks in Excel, download our practice workbook and follow us.

Download Template

Download this practice workbook for practice while you are reading this article.

Step-by-Step Procedure  to Track Stocks in Excel

To demonstrate the procedure, we consider the 5 most popular companies in the world. The name of those companies is in column B. After completing the stock tracker it will show as the image.

Step 1: Input Companies Name

In this step, we will input those companies’ names whose stocks we want to track.

• At first, title column B in cell B4 as Company Name.
• Now, write down your desired company’s names in the range of cells B5:B9. In our dataset, we consider Amazon, UPS, Microsoft Corp, Boeing, and Apple as our desired companies.

We have completed our first step to track stocks in Excel.

Step 2: Get Stocks Information Using Excel’s Built-in Feature

This is the most significant step in stock tracking. Here, we will extract all the necessary information about those companies’ stocks using the built-in feature of Excel.

• First, select the entire range of cells B5:B9.
• Then, in the Data tab, select Stocks from the Data Types group.

• You will see the companies’ name patterns will change, and it will get the complete name structure.
• Besides it, you will see a small widget pop-up icon will appear at the right corner of the selected names.

• If you click on it, you will get several fields listed there. Add according to your desire. We are going to add 8 fields according to our desire.
• For that, click on the widget pop-up icon, scroll down in the Field list with your mouse and select the Price option.

• You will see the Price of all five companies will add in the range of cells C5:C19.

• Now, entitled the cell C4 as Current Price.

• Similarly, add the Change(%), Changes, Market cap, 52 Week High, 52 Week Low, P/E, and Beta fields in columns D, E, F, G, H, and I respectively.
• Then, entitled the range of cells D4:I4 for the column headings as shown in the image.

We have completed the second step to track stocks in Excel.

Step 3: Insert Your Stocks Information

We have to input our stock information in our stock tracker. We need to input two vital Info, our stock quantity, and the purchase price. Besides it, we will also declare the selling price of our stocks.

• To input them, titled cells K4, L4, and M4 as No. of Holding Stocks, Purchase Price, and Target Selling Price.

• After that, write down the stock amount, their corresponding purchase price, and target selling price.

• Now, to calculate your investment, write down the following formula into cell N5.

• Double-click on the Fill Handle icon to copy the formula up to cell N9.

Our third step is completed.

Step 4: Track Status of Stocks

Now, we are going to do our main task, tracking the condition of our stock. We will be able to take the decision on whether the shares should sell or keep, after the completion of this step.

• First, we will evaluate the current value of our stocks. For that, set the title in O4 as Current Value and write down the following formula in cell O5.

• Then, double-click on the Fill Handle icon to copy the formula up to cell O9.

• Next, we are going to estimate the profit from the stocks. To get the profit, write down the following formula in cell P5.

• Similarly, double-click the Fill Handle icon to copy the formula up to P9.

• Now, we will make our final decision with the help of the IF function. Write down the following formula into cell Q5.

• After that, double-click on the Fill Handle icon to copy the formula up to cell Q9.

• You can also get the total value of your investment, current stock value, and profit using the SUM function.
• To calculate the total values, in cell N10, write down the following formula:

• Similarly, write down the corresponding formulas for cells O10 and P10 to get their total.

So, we can say our final step to track stocks in Excel is completed.

Step 5: Format Key Columns for Better Visualization

Though our stock tracking file is completed, it lacks a good presentation. As a result, we will face great difficulties when we will try to see any specific data in this sheet. To get a better outlook of our dataset, we will add conditional formatting in four of our key columns. They are the Change (%), Changes(Dollar), Current Investment, and Status columns.

• In the beginning, select the entire range of cells D5:D9.
• Then, in the Home tab, select the drop-down arrow of Conditional Formatting from the Styles group.
• Now, select Color Scales > Green-Yellow-Red Color Scale.

• The column’s cells will show in different colors.

• Similarly, apply the same conditional formatting for columns Changes(Dollar) and Current Investment.

• After that, for the Status column, select the range of cells Q5:Q9.
• Again, select the drop-down arrow of Conditional Formatting from the Styles group and choose the New Rule option.

• As a result, the New Formatting Rule dialog box will appear in front of you.
• Now, select the Format only cells that contain option.
• Then, set the first drop-down box menu as Specific Text and write down the text SELL in the empty box.
• After that, select the Format option.

• Another dialog box called Format Cells will appear.
• Format according to your desire. In our case, we choose the Font style as Bold and the Color, Automatic to Green.
• Finally, click OK.

• Again, click OK to close the New Formatting Rule dialog box.

• You will see the cell contains SELL, showing our formats.

• Similarly, apply the same type of conditional formatting with a different color for text HOLD. So that you can easily distinguish between the two texts.

Now, our stock tracking dataset gets a better outlook, and we can easily find the value of the key column.

Step 6: Insert Charts to Show Patterns

We will add two types of charts in our stock track datasheet to represent the data patterns of our prices and investment. We are going to add a column and a pie chart to our dataset.

• In the column chart, we will show the Current Price, Purchase Price, and Target Selling Price.
• Now, select the range of cells B4:C9, and L4:M9.
• After that, select the drop-down arrow of the Column or Bar Chart from the Charts group.
• Then, select the Clustered Column option from the 2-D Column section.

• The chart will appear in front of you. After that, click on the Chart Element icon and check the elements you want to keep. In our case, we checked only the Axes and Legend elements for our convenience. Set the Legend’s position on Top.

• You can also modify your chart style and texts from the Design and Format tab.
• We choose Style 8 for our chart. For that, select the Style 8 option from the Chart Styles group.
• Besides it, use the Resize icon at the edge of the chart for better visualization.

• Next, for the Pie chart, select the entire range of cells B4:B9 and N4:N9, and select the drop-down arrow of the Insert Pie or Doughnut Chart option.
• Now, select the 3-D Pie option.

• Then, adjust the chart style. We chose Style 9 for our chart and checked all the chart elements for the convenience of our chart.

• At last, select the range of cells B2:Q2 and choose the Merge & Center option from the Alignment group.

• Write down the title according to your desire. We set the title of our spreadsheet as Track Stocks.