How to Track Stocks in Excel (Download Free Template)

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 template while you are reading this article.


Steps for Creating Template 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.

How to Track Stocks in Excel


Step 1: Input Name of Companies

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.

Get Stocks Information into Track Stocks Using Excel’s Built-in Feature

  • 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.

Get Stocks Information into Track Stocks Using Excel’s Built-in Feature

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

  • Now, entitled the cell C4 as Current Price.

Get Stocks Information into Track Stocks Using Excel’s Built-in Feature

  • 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, andrespectively.
  • Then, entitled the range of cells D4:I4 for the column headings as shown in the image.

Get Stocks Information into Track Stocks Using Excel’s Built-in Feature

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

🔍 Things You Should Know

The built-in Stock option of Excel’s Data tab provides us a live update of the stock price. It usually extracts the information online and shows them here. As a result, when you open our sample template, Excel will automatically refresh the data. Besides it, if you try to create your stock tracker, the values may not match the image on that particular day. Don’t get panic. Just follow the procedure, and you will be able to create the stocks tracker.


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.

Insert Your Stocks Information to Track Stocks in Excel

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

Insert Your Stocks Information to Track Stocks in Excel

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

Insert Your Stocks Information to Track Stocks in Excel

Our third step is completed.


Similar Readings


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.
=C5*K5

Track Stocks Status in Excel

  • 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.

=O5-N5

Track Stocks Status in Excel

  • 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.

=IF(C5>M5,"SELL","HOLD")

Track Stocks Status in Excel

🔍 Illustration of the Formula

We are explaining the formula for cell Q5.

The name of the company in row 5 is Amazon. The IF function will check whether the value of C5 (Current Price) is greater than M5 (Target Selling Price). If the test gets a positive result, it will print SELL. Otherwise, the function will return HOLD.

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

Track Stocks Status in Excel

  • 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:

=SUM(N5:N9)

Track Stocks Status in Excel

  • 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.

Read More: Excel Invoice Tracker (Format and Usage)


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.

Format Key Columns for Better Visualization to Track Stocks

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

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

Format Key Columns for Better Visualization to Track Stocks

  • 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.

Format Key Columns for Better Visualization to Track Stocks

  • 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.

Format Key Columns for Better Visualization to Track Stocks

  • 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.

Format Key Columns for Better Visualization to Track Stocks

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

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

Format Key Columns for Better Visualization to Track Stocks

  • 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.

Format Key Columns for Better Visualization to Track Stocks

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.

Insert Charts to Show Patterns to Track Stocks

  • 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.

Insert Charts to Show Patterns to Track Stocks

  • 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.

Insert Charts to Show Patterns to Track Stocks

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

Insert Charts to Show Patterns to Track Stocks

  • 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.

How to Track Stocks in Excel

Finally, we can say our datasheet gets a better outlook and we are able to track stocks in Excel.


Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to track stocks in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo