Excel For Investment Tracking: A Comprehensive Guide

Keeping track of your investments is crucial, especially if you are trading in multiple stocks and executing several orders daily. A successful stock trader maintains a spreadsheet of their stock portfolio to register what stocks they are trading, what information they need to keep, and how their money is being managed.

Microsoft Excel is a popular tool for this purpose, offering a versatile and flexible platform to create a personalised investment tracker. With Excel, you can track the value of your stocks, mutual funds, ETFs, and more. You can also run custom reports, analyse trends, project tax liability, and even plan for retirement.

In addition to its versatility, Excel also provides privacy and simplicity. You can calculate and visualise the performance of your stocks, determine future performance, and assess risk, profit, and loss. Excel also has built-in functions to automatically calculate metrics such as standard deviation, percentage of return, and overall profit and loss.

This guide will take you through the steps to create an investment tracker in Excel, from inserting stocks and converting data to generating relevant information, adding investment details, and including your analysis formulas.

How to insert stocks into Excel

Microsoft Excel can be used to track your portfolio and investments with ease. You can use it to visualise the performance of your stocks at a glance, calculate future performance, and determine the degree of risk, profit, and loss. You can also use Excel to calculate the standard deviation, which is a complex formula that assesses risk.

• Open a new workbook or sheet in Excel and start typing in the tickers of the stocks you want to track into the first column. You can include the company name or fund name if you don't have the ticker symbol.
• Highlight the cells containing the tickers and go to the "Data" tab. Click on "Stocks" under "Data Types". Excel will convert the text into the Stocks data type and link it to an online data source.
• Create an Excel table to make extracting online information easier. To do this, go to "Insert > Table".
• With the cells still selected, click on "Stocks". If Excel finds a match between the text in the cells and its online sources, it will convert your text to the Stocks data type. You'll know they're converted if they have the stock icon.
• Select one or more cells with the Stocks data type, and the "Add Column" button will appear. Click on it and then select a field name to extract more information, such as the stock price, change in price, last trade time, previous close, or exchange.
• Click the "Add Column" button again to add more fields. If you're using a table, type the field name in the header row. For example, type "Change" in the header row for stocks, and the change in price column will appear.
• To see all the fields available for a company or fund, click the stock icon or select the cell and press "Ctrl+Shift+F5".
• If Excel is having trouble matching your text with data in its online sources, correct any spelling mistakes and press "Enter". Excel will then try to find matching information. Alternatively, click on the selector pane, search for data using a keyword or two, choose the data you want, and click "Select".
• You can also write formulas that reference data types or use the STOCKHISTORY function to get historical stock data.

Note: The Stocks data type is only available to Microsoft 365 accounts or those with a free Microsoft Account. You must also have the English, French, German, Italian, Spanish, or Portuguese editing language added to your Office Language Preferences.

How to convert data into stocks

To convert data into stocks in Excel, you can follow these steps:

Step 1: Inserting Stocks

Open Excel and create a new spreadsheet. In the first column, list the stocks you are holding or planning to purchase. You can use the company's full name, traded name, or ticker symbol. For example, let's say you own shares in Facebook (Meta), Google, Apple, and Tesla.

Step 2: Converting Data to Stocks

Select the data range containing your stock tickers. Go to the "Data" tab in the toolbar and click on "Data Types". Choose "Stocks" from the options. Excel will convert the tickers into their respective company stocks, displaying both the full business name and their traded name.

Step 3: Generate Relevant Information

Click on any cell containing a stock name in your list. An icon will appear, which you can click to access a drop-down list of relevant information. Select the data you want to generate, such as stock price, market capitalization, and percentage change. This information will be added to your spreadsheet. Note that Excel updates this data once daily after the market's closing hour.

Step 4: Add Investment Information

In this step, you will add details about your investments. Include the number of shares purchased for each stock and the amount spent initially on each. Then, calculate the worth of your investment in each company by multiplying the number of shares with the initial price per share.

Step 5: Calculate Current Values

To determine the current value of each stock you hold, multiply the number of shares by the stock's current price.

Step 6: Analyze Gains/Losses

Add another column to indicate whether you are gaining or losing on each stock. Set conditional formatting to visually represent the gain/loss. Calculate this by subtracting the initial investment from the current value of each stock.

By following these steps, you can effectively convert data into stocks and perform basic analyses on your investments using Microsoft Excel.

How to generate relevant information

Now that you have inserted the stocks into Excel and converted the data, it's time to generate relevant information. This is where you will start to see your spreadsheet come to life as a powerful investment tracking tool.

Click on any cell that contains a stock name in your portfolio. An icon will appear, and when you click on it, a drop-down list will appear. From this list, you can select the information you want to generate to create your investment portfolio.

The options include stock price, market cap, and percentage change. Select the data you want, and it will be added to your portfolio. It is worth noting that Excel updates this information once daily, after the market's closing hour, so it won't reflect hourly changes.

At this stage, you can also add your investment information, such as the number of shares purchased and the amount spent. You can then calculate the worth of your investment by multiplying the number of shares by the price per share.

You can also calculate the current value of each stock by multiplying the number of shares by the current price. This will allow you to see if you are gaining or losing on each stock.

Additionally, you can add a column to determine your target price and see how close or far you are from reaching it as a percentage. Visual aids, such as charts and diagrams, can also be inserted to help you understand your investment portfolio's distribution and what makes up your overall returns.

How to add your investment information

Now that you've set up your Excel spreadsheet and converted your data into stocks, it's time to input your investment information. This is where you'll add details about your investments, including the number of shares you own and the amount you spent.

First, list the number of shares you purchased for each stock in your portfolio. Next, record the initial price you paid for each share. Then, calculate the total worth of your investment in each company by multiplying the number of shares by the initial price per share.

For example, let's say you purchased 100 shares of Stock A at $10 per share. Your initial investment in Stock A is $1000.

Now, calculate the current value of each stock you hold. To do this, multiply the number of shares by the current market price per share. Using the previous example, if Stock A is now trading at $15 per share, your current investment value is $1500.

You can also calculate the percentage change in the stock price by subtracting the initial price from the current price, dividing that difference by the initial price, and then multiplying by 100. In this case, the percentage change would be 50%.

Additionally, you can calculate your total return on each stock by subtracting the initial investment amount from the current value. Using the same example, your total return on Stock A would be $500.

If you want to set price targets for buying or selling, you can create a “Sell/Hold" column with a formula such as =IF(Current Price > Target Price, "SELL", "HOLD"). This will help you make informed decisions about when to buy or sell your stocks.

You can also add other relevant information to your spreadsheet, such as the stock price, market capitalization, and percentage change. This information can be automatically generated by Excel once you've converted your data into stocks.

By inputting your investment information, you'll be able to track your portfolio's performance, calculate returns, and make more informed investment decisions.

How to add analysis formulas

Once you have set up your investment tracker in Excel, you can add analysis formulas to calculate the performance and degree of volatility of your investments.

Difference Formulas

To calculate the difference between an asset's current price and its entry price, you can insert a formula into a cell. First, click on the cell where you want the difference to appear and type the equals sign (=). Then, click on the cell containing the current price, followed by a minus sign, and then click on the cell that contains the entry price. Press enter, and the difference will appear in the designated cell. You can drag the formula to other cells to find the difference for each dataset.

Percent Return Formulas

The percent return is the difference between the current price and the entry price, divided by the entry price: (price-entry) ÷ entry. To calculate this, select the cell where you want the value to appear and type the equal sign. Then, type an open parenthesis and click on the cell with the current price, followed by a minus sign and the entry price. Next, type a forward slash to represent division and click on the entry price cell again. Press enter, and the percent return will appear. You may need to highlight the column, right-click, and select "Format Cells" to display the values as percentages.

Profit/Loss Formulas

The profit and loss formula is the difference multiplied by the number of shares. To create this formula, click on the cell where you want the value to appear and type the equals sign. Then, click on the cell that contains the difference and type the asterisk symbol (*) to represent multiplication. Finally, click on the cell with the number of shares and press enter. You may need to highlight the column, right-click, and select "Format Cells" to set the column to display as a dollar amount.

Standard Deviation Formulas

Standard deviation is a complex formula that assesses the risk of an investment. It measures how far returns are from their statistical average, allowing investors to determine the above-average risk or volatility of an investment. To find the standard deviation of a dataset, click on the cell where you want the value to appear and select "Insert Function" under the "Formulas" heading in Excel. Choose "Statistical" under "Select a Category" and scroll down to select "STDEV," then click OK. Highlight the cells for which you want to find the standard deviation, being careful to select only the return values. Click OK, and the standard deviation calculation will appear in the cell.

Break-Even Point Formulas

The break-even point is the stock price that ensures a net income of zero, with anything beyond that being pure profit. To calculate this, use the following formula:

= (((Buying Price*Shares Purchased+(Commission*2))/Shares Purchased)-Tax Rate*Buying Price)/(1-Tax Rate)

For an accurate prediction, consider categories such as buying price per share, the number of shares owned, commission paid to a brokerage service, and tax rates.

Frequently asked questions