Skip to main content

Posts

Showing posts with the label Stock Portfolio Tracker
Compare stock portfolio to market indexes

Compare stock portfolio to market indexes

As investors, we always want to see our portfolio grows over time. If a portfolio made a 1000$ of gain in one year, is it good enough? Should we gauge the portfolio's performance against an alternative investment, for instance, a market index? Should we aim to beat that index? In this post, we will see how to compare a stock portfolio to market indexes by using Google Sheets, Apps Script, and Google Data Studio. Select market indexes to compare Method to compare Guides Prepare data with Google Sheets and Apps Script Visualize benchmark in Google Data Studio Demo Conclusion Note References Select market indexes to compare In the context of stock investment, investors can compare the performance of their portfolios over a period of time with an alternative investment such as a market index, a portfolio of another investor, or even a single stock if they don't want any diversification, etc. In general, a market index is a good benchmark because it represents t
Stock Portfolio Tracker Dashboard With Google Data Studio

Stock Portfolio Tracker Dashboard With Google Data Studio

In the series of building personal stock portfolio tracker, we have learned how to use Google Sheets to register transactions . We have then used the pivot table and GOOGLEFINANCE function to compute the latest position of the stock portfolio . We have made a step further to use Apps Scripts to compute automatically and daily the stock portfolio's evolution . However, after all, we have several tables of data as the result which do not tell any story yet. We need to present those data in graphs to understand the portfolio's performance and make improvements accordingly. We can effectively plot graphs in different aspects directly in Google Sheets as it provides many charting tools. However, in my experience, having charts and data in the same spreadsheet is not very convenient because charts and tables tend to overlap each other and of lack of interactivity. We should have a dedicated dashboard to have an overview of the stock portfolio and we can do it greatly with Google Data
Compute daily evolutions of a stock portfolio with Google Sheets and Apps Script

Compute daily evolutions of a stock portfolio with Google Sheets and Apps Script

When it comes to investment, it is not only important to know the up-to-date state of portfolio but also to track its evolution day by day. We need to know on a specific day, how much money has been invested in the portfolio, the current market value of owned shares, the available cash and the current profit. Visualizing those historical data points on a time-based graph helps us to identify which transactions were good and which were bad. This post shows how to compute automatically those historical data points by using data in Transactions sheet and the built-in GOOGLEFINANCE function of Google Sheets. A sample spreadsheet can be found in this post Demo stock portfolio tracker with Google Sheets . You can take a look at the sample spreadsheet to have an idea of how the data is organized and related. It is possible to make a copy of the spreadsheet to study it thoroughly.

Demo stock portfolio tracker with Google Data Studio

As explained in the post Create personal stock portfolio tracker with Google Sheets and Google Data Studio , a personal stock portfolio tracker consists of 2 main elements: a spreadsheet in Google Sheets and an interactive dashboard in Google Data Studio. The dashboard below is built with Google Data Studio and visualizes data stored in the spreadsheet that can be found in the post Demo stock portfolio tracker with Google Sheets . The dashboard below is not an image, it is a real one and is interactive. You can change some filters to see data from a different perspective. For instance, you can change the date range or select a particular stock. Click here to open it in Google Data Studio NOTE: An enhanced version was published at Create personal stock portfolio tracker with Google Sheets and Google Data Studio . Note Disclaimer Note To better understand the overall concept, please check out this post Create personal stock portfolio tracker with Google Sheets and Google

Demo stock portfolio tracker with Google Sheets

As explained in the post Create personal stock portfolio tracker with Google Sheets and Google Data Studio , a personal stock portfolio tracker consists of 2 main elements: a spreadsheet in Google Sheets and an interactive dashboard in Google Data Studio. You can take a look at the sample spreadsheet below to have an idea of how the data is organized and related. It is possible to make a copy of the spreadsheet to study it thoroughly. NOTE: An enhanced version was published at Create personal stock portfolio tracker with Google Sheets and Google Data Studio . Make a copy Note Disclaimer Make a copy Click here to make a copy Note To better understand the overall concept, please check out this post Create personal stock portfolio tracker with Google Sheets and Google Data Studio . Disclaimer The post is only for informational purposes and not for trading purposes or financial advice.
Manage Stock Transactions With Google Sheets

Manage Stock Transactions With Google Sheets

The first task of building a stock portfolio tracker is to design a solution to register transactions. A transaction is an event when change happens to a stock portfolio, for instance, selling shares of a company, depositing money, or receiving dividends. Transactions are essential inputs to a stock portfolio tracker and it is important to keep track of transactions to make good decisions in investment. In this post, I will explain step by step how to keep track of stock transactions with Google Sheets.
Monitor stock portfolio with Google Sheets (Pivot table and GOOGLEFINANCE function)

Monitor stock portfolio with Google Sheets (Pivot table and GOOGLEFINANCE function)

As an investor, it is important to know the latest state of the stock portfolio. We need to know what stocks currently owned in the portfolio, how many shares for each one, how much dividend or gain contributed so far by each stock, etc. As we have registered stock transactions in a spreadsheet with Google Sheets, we can easily have the latest update from the stock portfolio by using pivot tables and GOOGLEFINANCE function. Use a pivot table to group transactions by symbols Configure the pivot table Positions Use GOOGLEFINANCE function to get real-time information for stocks Conditional formatting columns Demo Note References Use a pivot table to group transactions by symbols The pivot table helps to see relationships between data points. To see how each stock contributes to the portfolio, we will create a pivot table that originated from the Transactions sheet. Select the Transactions sheet. Select the 5 columns A:E . In the menu at the top, click Data and then