Skip to main content

Posts

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 Disclaimer 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

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. 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

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. As mentioned in the post Create personal stock portfolio tracker with Google Sheets and Google Data Studio , we will use Google Sheets as a database to register transactions and here is how: Define the structure of transactions Use Google Sheets to register transactions Demo Note References Disclaimer Define the structure of transactions In the example, I assume that a transaction generally has 5 main attributes: Date : It is the moment when a transaction happened. Type : It can be one of the following values: DEPOSIT : When money is added to the portfolio BUY :

Useful Apps Script Snippets

Here are some useful snippets of Apps Script. New snippet will be added progressively. Add a menu on opening the spreadsheet Hide a sheet by name Show a sheet by name Create a new sheet or clear content of an old sheet if exists Add a menu on opening the spreadsheet Hide a sheet by name Show a sheet by name Create a new sheet or clear content of an old sheet if exists

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 Disclaimer 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