How to Track Stocks in Excel (Download Free Template)

To demonstrate how to track stocks, let’s consider some of the most popular companies in the world, with the names of those companies provided in column B. After completing the stock tracker, it will show as the image.

Step 1 – Inserting Name of Companies in Excel

• Title column B in cell B4 as Company Name.
• 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.

Step 2 – Extracting Stocks Information Using Excel’s Built-in Feature

• Select the entire range of cells B5:B9.
• 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.
• 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. We are going to add 8 fields.
• 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 added in the range of cells C5:C9.

• 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.
• Give the columns their headings.

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 a live update of the stock price. It usually extracts the information online and shows them here. As a result, when you open the sample template, Excel will automatically refresh the data. If you try create your stock tracker, the values may not match the image on that particular day.

Step 3 – Inserting Our Stocks Information

• Write down the stock amount, their corresponding purchase price, and target selling price.

• Write down the following formula into cell N5:

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

Step 4 – Tracking Status of Stocks

• Set the title in O4 as Current Value and write down the following formula in cell O5:

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

• To get the profit estimate, write down the following formula in cell P5:

=O5-N5

• Write down the following formula into cell Q5:

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

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.

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

=SUM(N5:N9)

• Write down the corresponding formulas for cells O10 and P10 to get their total.

Step 5 – Formatting Key Columns for Better Visualization

• Select the range of cells D5:D9.
• In the Home tab, select the drop-down arrow of Conditional Formatting from the Styles group.
• Now, select Color Scales and add Green-Yellow-Red Color Scale.

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

• Apply the same conditional formatting for columns Changes (Dollar) and Current Investment.

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

• The New Formatting Rule dialog box will appear in front of you.
• Select the Format only cells that contain option.
• Set the first drop-down box menu as Specific Text and write down SELL in the empty box.
• Select the Format option.

• Another dialog box called Format Cells will appear.
• Format accordingly. We chose the Font style as Bold and the ColorGreen.
• Click OK.

• Click OK to close the New Formatting Rule dialog box.

• You will see the cell containing SELL showing the formatting.

• Apply the same type of conditional formatting with a different color for text HOLD.

Step 6 – Inserting Charts to Show Patterns

• In the column chart, we will show the Current Price, Purchase Price, and Target Selling Price.
• Select the range of cells B4:C9, and L4:M9. (Hold Ctrl while selecting columns to do so).
• Select the drop-down arrow of the Column or Bar Chart from the Charts group.
• Select the Clustered Column option from the 2-D Column section.

• The chart will appear in front of you. 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.

• Modify your chart style and texts from the Design and Format tab.
• We chose Style 8 for our chart from the Chart Styles group.
• Use the Resize icon at the edge of the chart for better visualization.

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

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

• Select the range of cells B2:Q2 and choose the Merge & Center option from the Alignment group.

• Write down the title for the sheet. We set it as Track Stocks.

Download Template

Download this template which you can use and expand.

Related Articles

• How to Import Stock Prices into Excel from Yahoo Finance
• How to Get Stock Prices in Excel
• How to Get Live Stock Prices in Excel
• How to Get the Current Stock Price of India in Excel
• How Do You Automatically Update Stock Prices in Excel
• How to Import Stock Prices into Excel from Google Finance

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that. Read Full Bio

Reply

Hello SUBBARAMAN Thanks for reaching out and sharing your requirements. Your appreciation means a lot to us. Thanks once again. You want to analyze the Indian Stock Markets. To achieve your goal, you only need to use the Indian company names, and the rest of the procedure is described in the article. Like the following GIF, I generated the intended data for some noted Indian Companies.

So, type the Indian company names and follow the steps of this article. Good luck! Regards
Lutfor Rahman Shimanto

Reply

Hello VARUN Thanks for reaching out and sharing your requirements. Your appreciation means a lot to us. Yes, We can provide information on the Indian stock market with charts in dollars (INR) instead of dollars ($). You can download this template below for your help. Additionally, I’m also writing down the steps so that you can modify it yourself. Download Excel file: Track-Indian-Stock-Market.xlsx Use these steps for indian stock market tracking:

1. Add Indian Stock company Names.
2. Select the range (i.e. B5:B9).
3. Go to the Data tab >Data Types group >Stocks.
   It will automatically update the Stock Tracker names. However, the values will show an Error Warning.
4. Select the cell (i.e.C5) > Click the drop-down menu beside the error warning > Click Update format.
   It will automatically update the price in INR format.
5. Use the Fill handle feature to apply the change in the column.

Repeat the same process for other columns where necessary till J or Beta Column.

To update the format of Our Stock information section (Column K to P), follow these steps:

1. Select the Current Price Column (i.e. B5:B9)
2. Go to the Home tab >Clipboard group >Format Painter icon.
3. Select the range (i.e. L5:M9) to apply the format painter. You will notice the paint icon beside the cursor.

It will apply the same currency format to the applied range. Use the same process for Column O and P.

To update the format with Conditional formatting of Current Investment Column, copy the formatting of Changes (INR) column using Format Painter and paste it. It will automatically update both currency format and conditional formatting.

Note: The charts will update automatically.

Hope this helps you out.

Ishrak Khan
ExcelDemy