Introduction
Do you remember the movie “The Wolf of Wall Street”? Jordan Belfort’s life was a roller coaster of money, parties, and of course, stocks. But behind the scenes, investing is not always about the ups and downs of short-term day trading. For many investors, it’s a strategic game of patience, aiming for consistent long-term growth in their investments.
Both beginners and seasoned investors face the constant challenge of effectively managing their portfolio. It’s not just about the assets to invest in; it’s also about tracking them, understanding their historical context, and making informed decisions for the future. That’s where our Portfolio Tracker spreadsheet model comes into play, available in both Microsoft Excel and Google Sheets. In this article, we will guide you on effectively managing your portfolio using a model that updates stock data in real-time. By the end of this article, you will know how to:
- Record each transaction with an “Investment” and “Sales” tab.
- Track the performance of your portfolio.
- Monitor potential investment opportunities and track the market with a watchlist.
- Analyze historical data and performance in a comprehensive dashboard.
While Jordan and his team led a luxurious life based on immediate gains in the movie, many investors quietly grew their wealth through a long-term vision. They understand the stability of diversified portfolios and the importance of meticulously recording their investments.
To illustrate how to use our model, we will use a hypothetical scenario featuring Jordan Belfort. Imagine a transformed Jordan, leaving behind the fast-paced world of quick transactions. To embark on this new journey, Jordan needs to buy new stocks, and that’s where our “Investment” tab comes into play.
Model
Investment Journal
Imagine a calm Jordan Belfort sitting and analyzing a potential stock or asset. He has chosen Alphabet Inc. (GOOG) as a promising long-term investment and wants to buy 500 units. This transaction can be recorded in the “Investment Journal” tab. To do this, enter the transaction date, the name of the stock (in this case, Alphabet Inc.), the number of shares acquired, the unit price, and any transaction or commission fees, if applicable. It’s worth noting that while transaction fees are a fixed amount, the commission is a percentage based on the purchase price.
As Jordan continues his investment journey, he might buy more of the same stock or diversify his investments into other stocks like Amazon (AMZN) or Apple (AAPL). He might even venture into the world of cryptocurrencies by buying bitcoins. This model automatically calculates the transaction amount and provides details about the acquired stock, including its symbol, type, sector, and currency.
These data also generate a preview section, populated with interactive charts to facilitate the analysis of your investment patterns. Investors can track their overall investments by type, sector, and individual stock. Additionally, this section provides a chart to visualize the evolution of investments, detailing monthly purchases per stock.
Real-time Stock Data
A remarkable feature of this spreadsheet is its integration with Excel’s “stock data” format, which allows for real-time retrieval of stock information. For those unfamiliar, this feature enables users to retrieve stock-related information, ensuring that their portfolio reflects the current dynamics of the market. To add new stocks using this feature:
- Enter the desired stock name in the “Stock” field.
- Select the new stock and navigate to Excel’s “Data > Data Types” section, choosing “Stocks” from the dropdown that appears.
By following these steps, you ensure that your spreadsheet stays up to date with the constant fluctuations of the stock market and avoids guesswork and manual updates.
This feature provides you with a comprehensive set of data. It encompasses everything from the current price to basic day-to-day indicators such as close, high, and low values.
You can get a glimpse of your asset’s annual performance through 52-week highs and lows and understand market structure through market capitalization. Other important data points are also tracked by the model, such as change, trading volume, and P/E ratio, to grasp valuation nuances and general details, like the stock exchange where the stock is listed. The description of each data point can be found in the “Fields” tab, allowing you to analyze and strategize your asset’s performance.
Sales Journal
After some time and observation, Jordan decides it’s time to sell a portion of his assets to secure his gains. In our hypothetical situation, Jordan chooses to sell half of his Alphabet Inc. (GOOG) shares. Recording this transaction is very similar to recording a purchase. The difference lies in the fact that we are selling stocks instead of buying them.
Start by entering the sale date in the “Sales Journal” tab. Then, select the stock you sold from the dropdown menu. This menu is updated based on the information you previously entered in the “Investment Journal” tab. Next, enter the number of units sold, the price at which they were sold, and any applicable fees or commissions. This setup allows you to track the profit or loss from the sale.
As time goes on and Jordan makes more sales, whether it’s stocks or cryptocurrencies, this tab provides a clear record. It gives him an overview of his transactions, helping him understand and track the success of his sales decisions. There are also charts in this section that can display gains or losses per stock, offering a visual representation of successful and less successful transactions. Once all the details have been entered in the “Investment” and “Sales” tabs, the “Portfolio” tab is ready to track current investment positions.
Portfolio
Consider the “Portfolio” tab as a bird’s-eye view of your investments, much like when Jordan takes a step back to assess the overall financial landscape of his activities. The “Portfolio” tab dynamically reflects the data you have entered in the “Investment” and “Sales” tabs. Recording a purchase makes that stock part of your asset list. Similarly, recording a sale automatically deducts the corresponding amount from your portfolio.
For example, even though we recorded a purchase of 500 shares of Alphabet Inc. (GOOG) for Jordan, the portfolio now holds only 250 shares due to the previous sale. Additionally, you will also have a breakdown of your asset details, including:
- The types of stocks you own.
- The number of units for each stock.
- The average cost per unit.
- The current market value.
This market value is calculated based on real-time data, ensuring an up-to-date view of your assets’ value. The tab also estimates the total value of each held stock, the amount invested, and your gains (or losses) for each investment. Additionally, there is a mini-chart showing the stock’s performance over the past year.
This tab also provides a clear view of the performance of each investment, highlighting underperforming or outperforming investments. Furthermore, at the top of the tab, the “Portfolio Overview” section offers charts showing the breakdown of your portfolio by stock type and investment sectors. It also details the performance by industry and displays the total return of your current portfolio.
Use this tab as an instant dashboard to track the performance of your portfolio and its diversification. Once completed, we’ll delve into this further in a moment. But here, you can monitor the health of your investments and identify areas that require further examination. Centralizing the data from the “Investment” and “Sales” tabs in this tab aids in understanding your current position and portfolio profile. As you progress on your financial journey, it is also crucial to know how to stay ahead and stay informed about potential future acquisition and sale opportunities. If you want to optimize your portfolio and returns, that’s where the “Watchlist” tab comes into play.
Watchlist
The watchlist is an essential tool for investors. Whether you are a seasoned professional like Jordan or a novice investor, this tab provides an organized space to track the stocks you plan to invest in the future.
For example, let’s say Jordan hears about a potential rise in Tesla (TSLA). He can quickly add it to his watchlist. To do this, enter the stock’s name in the table, and if necessary, follow the same steps as before to format the field as a “stock” data type. Once done, the model will provide real-time data about Tesla, such as its current price, 52-week highs/lows, and past performance trends. This tab offers a concise overview of the stock’s position in the market.
The “Watchlist” tab also provides a “Historical Performance” section. You can select a stock from your watchlist, set a specific period, and choose an interval type (daily, weekly, or monthly). Then, the model will generate a dynamic chart that plots the stock’s performance throughout the chosen period.
By maintaining such a watchlist, Jordan—or you—can, at a glance, decide on the right time to jump into a new investment or wait. This proactive approach ensures that investors are always ready to seize opportunities as they arise.
Now, we have covered each step of the investment process: buying, selling, portfolio management, and watchlist for future investment opportunities. This model centralizes all this information into a comprehensive dashboard to help you monitor and track your portfolio’s performance while studying possible alternatives and developing strategies for future growth.
Dashboard
The “Dashboard” tab combines data from the other sections to provide an overview of your portfolio’s performance and make the complexity of numbers much more manageable.
The “Overview” section gives a clear view of your financial situation, detailing the total value of your assets, the total amount invested, and the resulting returns in both dollars and percentages. Additionally, it shows how your assets are distributed in terms of value and the returns they have accumulated. This section is essentially a financial health check, revealing how your investments have performed and where they currently stand.
The next section, “Portfolio Performance,” delves into the nuances of your returns. With bar charts segmented by “Stock,” “Sector,” and “Stock Type,” you get a nuanced view of each sector’s or stock’s performance. This visually represents where the gains come from and which sectors or stocks are the real growth drivers.
Next, you will find a comprehensive analysis of individual stocks in the “Portfolio Details” section. Select a stock from your portfolio, and the dashboard will display everything from the number of units held to the total value and percentage returns to current and historical price data. This section is the detailed anatomy of your stock, providing insights into its past, present, and future potential.
This section is customizable, allowing you to choose the stock, period, and interval type—daily, weekly, or monthly—for monitoring. Additionally, you have the flexibility to add or remove references for comparison. To make these changes, go to the “Fields” tab and navigate to the “Historical Comparison” section, where you can modify the references used in the dashboard for performance comparisons.
The dashboard is not just a collection of numbers and charts; it’s the narrative of your financial journey and investments. Each section is a chapter, and each data point is a story that helps you make informed decisions for a prosperous financial future.
Conclusion
Every investor can benefit from a structured and systematic approach to portfolio management. With our Portfolio Tracker spreadsheet model, the complex world of investments becomes a little simpler. Whether you’re a seasoned investor or just starting out, remember to invest smartly, track meticulously, and stay informed. We would love to hear about your experiences in the comments section. And remember, this model is available for download. Let us know what you think and what you’d like to see next. Happy investing!
