Excel Strategies For Building A Robust Investment Portfolio

Keeping track of your stock investment portfolio is very important. Excel spreadsheets can be used to keep track of your investments, calculate performance and the degree of volatility, and help you visualize the performance of your stocks at a glance. Excel can also connect to real-time stock-tracking information, so you can always see how much your stocks are worth. You can use Excel to create an investment-tracking spreadsheet to calculate future performance and degrees of risk, profit, and loss, as well as compare different types of investments across categories, futures, and types.

Inserting stocks into Excel

To start building your investment portfolio in Excel, open a new spreadsheet and list the stocks you are holding or planning to purchase. You can use the companies' business names, traded names, or the ticker symbols they are listed under. For example, you could list stocks such as Amazon (AMZN), Google (GOOG), Microsoft (MSFT), Walmart (WMT), and Capital One (COF).

Once you have your list of stocks, you can start to convert this data into a format that Excel recognises as stocks. Highlight the tickers and go to the "Data" section of the toolbar. Within the "Data Types" section, select "Stocks". This will update the tickers with the full company name and ticker.

Now that you have converted the data into a stock data type, you can start to pull relevant information. Select all the tickers, and a widget will pop up on the right with options for the stock data you can input. By selecting "Price", for example, a new column will be created with the current prices of each stock.

Other useful information you can pull includes the day's change in percentage, total market cap, 52-week high, 52-week low, P/E ratio, and beta. The P/E ratio measures the price of the stock relative to its earnings or profit, while beta measures the stock's movement relative to the overall market.

With the stock data in place, you can now add your own investment information. This includes the number of shares you own, your average price per share, and the total amount invested in each stock.

From here, you can start to calculate metrics such as the current equity value, total return, and whether you should sell or hold each stock. For example, you can create a "Sell/Hold" column with the formula =IF(Current Price > Target, "SELL", "HOLD") to indicate whether to sell or hold the stock based on your price target.

By following these steps, you can effectively insert stocks into your Excel investment portfolio and begin tracking and analysing your investments.

Convert data to stocks

Now that you have a list of stocks, you can convert the data into a format that Excel can understand. This is a straightforward process.

Firstly, select the data you want to convert. Then, from the top navigation bar, select 'Data'. From the 'Data' toolbar, you should see a section called 'Data Types'. Click on 'Stocks' and your tickers should update with the full company name and ticker.

Once you have converted the data into stocks, you can pull relevant information. To do this, select all the tickers, and a widget will pop up on the right with options for the stock data you can input. Click on the widget, and select the data you want to pull in. Each piece of data you select will add another column of data to your spreadsheet.

The first piece of information you will probably want to pull is the price of the stock. You can also add the day's change %, total market cap, 52-week high, 52-week low, P/E ratio, and beta. The P/E ratio measures the price of the stock relative to its earnings/profit, while beta measures a stock's movement relative to the overall market.

You can also add your own investment information, such as the number of shares you own and your average price per share. With this information, you can calculate the total amount invested in each stock at the purchase price by multiplying the average price paid by the number of shares.

You can also calculate your current total equity in each stock by multiplying the current price by the number of shares you own.

Finally, you can calculate your total return on each of the stocks by subtracting the investment amount from the current equity value. You can also add conditional formatting to highlight gains and losses, either by using a colour scale or cell rules for positive or negative returns.

Generate relevant information

To generate relevant information for your investment portfolio in Excel, you will need to follow these steps:

• Decide on the data you want to include: This includes the date, entry, size (number of shares), closing prices for specified dates, the difference between the closing price and entry price, the percentage return, profit and loss for each periodic closing price, and the standard deviation. You can also include the account name, stock ticker symbol, name of the investment, and category.
• Insert the stocks into Excel: Open Excel or Google Sheets and create a new spreadsheet. Input the stocks you are holding or planning to purchase. You can use the company's full name, traded name, or ticker symbol.
• Generate relevant stock information: Convert the company names into stocks, which will generate relevant stock information. To do this, select the data, go to the top navigation bar, select "Data", and click on "Stocks". The company stocks will be converted into their full business name and traded name.
• Select the information you want to generate: Click on any cell containing a stock name in your portfolio, and select the information you need to generate to create your investment portfolio. Typically, this includes stock price, market cap, and percentage change. You can also add dividend payments, but this information cannot be automatically retrieved and must be manually added.
• Calculate additional metrics: Excel can automatically calculate other metrics such as an asset's or portfolio's standard deviation, percentage of return, and overall profit and loss. You can also calculate the break-even point, which is the price a stock needs to achieve to ensure a net income of zero.
• Utilize financial data APIs: To automate the process of monitoring stock prices and evaluating portfolio performance, you can use financial data APIs (or market data APIs) in Excel. These APIs provide real-time access to data such as stock prices, company finances, and economic metrics. By linking your Excel spreadsheet to an API, you can automatically fetch and refresh market data, ensuring your portfolio assessments are always current.

Add your investment information

Now that you have set up your Excel spreadsheet and converted the data into stock data types, it's time to add your investment information. This is where you will input the number of shares you own of each stock, how much you paid for them, and the total value of your current holdings.

Firstly, create columns for the number of shares, the price per share, and the total amount spent on each stock. You can calculate the total amount spent by multiplying the number of shares by the price per share.

Next, you can calculate the current value of your holdings. To do this, multiply the current price of each stock by the number of shares you own. This will give you the total value of your holdings at the current market price.

You can also calculate the overall value of your portfolio by summing up the total value of each stock holding. This will give you a sense of the total worth of your investments.

Additionally, you can calculate the percentage return on your investments by subtracting the total amount spent from the current value and then dividing that number by the total amount spent. This will give you the percentage return on your investments, helping you understand your profits or losses.

You can also use conditional formatting to highlight cells with gains or losses, making it easier to visualize the performance of your portfolio. For example, you can use colour scales or cell rules to indicate positive or negative returns.

By adding your investment information to your Excel spreadsheet, you can easily track the performance of your portfolio and make more informed investment decisions.

Calculate profit and loss

To calculate profit and loss in Excel, you can use a simple formula:

Profit = Income - Expenses

Loss = Expenses - Income

• Set up your spreadsheet: Create a table with columns for "Stock", "Shares", "Price per Share", "Total Investment", "Current Price", and "Profit/Loss."
• Enter stock information: In the "Stock" column, list the stocks in your portfolio or the stocks you are planning to purchase. You can use the business name, traded name, or ticker symbol.
• Enter the number of shares: In the "Shares" column, input the number of shares you own or plan to purchase for each stock.
• Calculate the total investment: Multiply the "Price per Share" by the "Shares" to get the "Total Investment" for each stock.
• Update current prices: Use Excel's built-in "Stocks" feature or connect to a real-time stock tracking source to get the current market price for each stock.
• Calculate profit or loss: Subtract the "Total Investment" from the "Current Price" for each stock to get the profit or loss. A positive value indicates profit, while a negative value indicates a loss.
• Analyze and visualize: You can now analyze and visualize your portfolio's performance. Use conditional formatting to highlight cells with gains or losses, making it easier to identify profitable or underperforming stocks.

Additionally, you can calculate the average cost per share for multiple buy/sell actions over a period. Here is a formula suggested by a user on the Microsoft Community Hub:

> =SUMPRODUCT(Table[BUY QTY];Table[Cost])+SUMPRODUCT(Table[SELL QTY];Table[Cost]))/SUM(Table[[BUY QTY]:[SELL QTY]])

This formula multiplies the buy quantities with the cost, adds the sell quantities multiplied by the cost, and then divides the result by the total quantities bought and sold.

Frequently asked questions