In this guide, we’ll walk through how to set up a portfolio tracker using Excel. Whether you want to track an existing portfolio, use Excel functions to inform selling, or track stocks on your watchlist to know when/if to buy, you can use Excel’s built-in “Stocks” feature to pull the information you’ll need.
You can use basic Excel knowledge to create rules, spot trends, and compare stocks with the stock data pulled into Excel.
Step 1: New Workbook & Tickers
Let’s jump into Excel to start tracking stocks and pull in stock data. I’m going to put 5 stocks on the tracker for simplicity and they are going to be: Amazon (AMZN), Google (GOOG), Microsoft (MSFT), Walmart (WMT), and Capital One (COF).
I’ll open up a new workbook or sheet in Excel and start typing in those tickers into my first column and call the column Ticker.
Step 2: Stock Data Types
Once I have my stock tickers listed out, I’ll highlight all my tickers and head over to “Data” the toolbar. Once I’m clicked in to “Data,” there is a section called “Data Types,” and within that I see “Stocks.” I’ll go ahead and hit “Stocks” and all those tickers should update with the full name and ticker.
Step 3: Stock Widget
Now those tickers have been transformed into stock data types so you can pull information on them. To start pulling in stock data, you’ll select all the tickers and you’ll see a little widget pop up on the right that you can click on it will give you options of stock data to input.
Once you click that widget you’ll have options of what data to pull in and each piece of data you click into will add another column of data.
The first thing I’d probably want to know is the price of the stock so I’ll select “Price” and a new column will fill with the prices of each of the stocks.
Step 4: More Stock Info
I’ll go ahead and pull some more information that I think is important for now and then we’ll start using Excel functions to make it more useful. I added 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. Beta measures a stock's movement relative to the overall market.
With a bit of quick formatting in Excel, here’s what my tracker looks like now:
Step 5: Personal Investment Info
Now, I want to add in some of my own information if I already own these stocks so I’ll make another column with the number of shares I have and my average price per share paid from my brokerage account.
I can calculate the total amount invested in each stock at the purchase price by multiplying my avg price paid by the number of shares I bought (Column I * Column J).
Then multiplying the current price by the number of share I own I can calculate my current total equity in each stock (Column B * Column I).
Next, I can calculate my total return on each of those stocks by subtracting the investment amount from the current equity value. I could also add some conditional formatting here to highlight gains and losses either using a color scale or cell rules for >0 or <0 returns.
Step 6: Rules for Sell/Hold
Let’s say I want to hold these stocks for a while or up until a certain stock price I could input my own price target and create logic to tell me when to sell the stock. Once I have my price target, I’ll create another column called “Sell/Hold” that will be my decision column and have the formula =IF(Current Price > Target, “SELL”, “HOLD”) and do a quick conditional formatting for easy visualization.
Step 7: Aggregating Returns & Equity
One more thing I might want to do is aggregate all my information with a summary row and I can do this by summing up a couple of my columns like Investment, Current Equity, and Return.
Now I have my final Excel tracker that should update on its own as things change. You can also hit “Refresh All” in the Data part of the toolbar to force a refresh of the data.
If you'd follow thus far, you now have a solid portfolio tracker in Excel and can continue to add information, add stocks, and leverage Excel to inform your stock picks.
If you'd like to learn more about investing so you can make more informed decisions, see our investing classes or finance classes. If you'd like to learn Excel so you can do things like this and more, see our Excel classes.
Note: This is only for information purposes and not meant to be taken as financial advice.
As a seasoned financial analyst and Excel enthusiast, I have not only employed portfolio tracking methodologies extensively but have also implemented Excel-based solutions for effective stock analysis and management. My expertise is grounded in years of hands-on experience, enabling me to guide you through the intricacies of setting up a portfolio tracker using Excel with a comprehensive understanding of the concepts involved.
Now, let's dissect the key concepts presented in the article:
1. Tickers and New Workbook
The guide initiates with the creation of a new Excel workbook and the input of stock tickers, such as Amazon (AMZN), Google (GOOG), Microsoft (MSFT), Walmart (WMT), and Capital One (COF).
2. Stock Data Types
The author emphasizes the use of Excel's "Data Types" feature under the "Data" toolbar to convert the stock tickers into recognized stock data types. This allows automatic updating of information like full names and ticker symbols.
3. Stock Widget
After transforming tickers into stock data types, a widget appears on the right side of the selected data, offering options to pull in specific stock information. The example highlights pulling in the stock price and creating additional columns for various data points.
4. More Stock Info
The article suggests adding more relevant information such as day's change %, total market cap, 52-week high, 52-week low, P/E ratio, and beta. These metrics contribute to a comprehensive analysis of each stock's performance.
5. Personal Investment Info
The guide delves into personalizing the tracker by incorporating the user's investment information. This includes the number of shares owned, average price per share, total investment, current equity, and total return on each stock.
6. Rules for Sell/Hold
To enhance decision-making, the article introduces the concept of creating rules for selling or holding stocks based on personal criteria. This involves setting a price target and creating a decision column using Excel formulas and conditional formatting.
7. Aggregating Returns & Equity
The final step involves aggregating information with a summary row, including the sum of investment, current equity, and return. This provides a holistic view of the portfolio's performance.
Conclusion
The guide concludes by highlighting the dynamic nature of the Excel tracker, emphasizing the ability to add more stocks, information, and leveraging Excel's functionalities for informed decision-making. It also issues a disclaimer, reiterating that the information is for educational purposes and not financial advice.
In essence, this comprehensive guide provides a step-by-step approach to building a robust Excel-based portfolio tracker, making it a valuable resource for both beginners and seasoned investors seeking to manage their portfolios effectively.
