Skip to main content

Create personal stock portfolio tracker with Google Sheets and Google Data Studio

The LION stock portfolio tracker is a personal stock portfolio tracker built with Google Sheets and Google Data Studio. The transactions of a stock portfolio are registered in Google Sheets. The tracker uses the GOOGLEFINANCE built-in function in Google Sheets to have the historical and current prices of stocks. The tracker has a small Google Apps Script program to do some complex computation, such as generating automatically the stock portfolio's daily change and comparing it with alternative investments. The stock portfolio is then visualized on a beautiful and interactive dashboard with Google Data Studio. This guide shows how to create a personal stock portfolio tracker with Google Sheets and Google Data Studio.

LION stock portfolio tracker dashboard in Google Data Studio

Concept

The concept of a stock portfolio tracker with Google Sheets and Google Data Studio

A LION stock portfolio tracker consists of 3 main components: a spreadsheet in Google Sheets, a Google Apps-Script program in the same spreadsheet, and a dashboard in Google Data Studio.

  • In the spreadsheet, users need to insert data in 3 sheets: Configuration, Transactions, and Companies. The tracker will use data from these 3 sheets to measure the performance and the evolution of the stock portfolio. These measurements are stored in these others sheets: Overview, Values, Evolutions, Buy-Sell Evaluation, Benchmarks, and Symbols.
    • The Configuration sheet contains settings for the stock portfolio tracker. Users can change the values of these parameters.
    • The Transactions sheet contains transactions of the stock portfolio.
    • The Companies sheet contains Name, Industry and Sector of the stocks presented in the portfolio. The information is not provided by the GOOGLEFINANCE function.
    • The Values sheet presents the latest state for each stock presented in the portfolio.
    • The Evolutions sheet contains the daily states of the portfolio from the first transaction date until today.
    • The Buy-Sell Evaluation sheet contains data that helps to evaluate the efficiency of BUY and SELL transactions for a stock.
    • The Benchmarks sheet contains data that helps to compare the stock portfolio with 2 alternative investments.
    • The Symbols sheets contain historical prices of stocks. Each stock presented in the portfolio has its own historical prices sheet.
  • In the spreadsheet, there is a small Google Apps-Script program that can do some complex computations that are not feasible with the basic formulas of the spreadsheet. Most importantly, it helps to generate historical prices sheets for all stocks, compute the Evolutions, Buy-Sell Evaluation, and Benchmarks sheets. The script adds the menu LION Stock Portfolio Tracker to the spreadsheet's toolbar.
  • These sheets are connected as data sources to the dashboard in Google Data Studio. The dashboard has 5 pages: Overview, Diversification, Dividend, Buy-Sell Evaluations, and Benchmarks.

Demo

Who should use LION Stock Portfolio Tracker?

In making the LION stock portfolio tracker, I hope it can be useful for every investor. However, as I am an individual investor and my strategy is to invest in dividend-paying stocks in the long term, I think this stock portfolio tracker will best suit investors having a similar strategy. Although I have tried my best to make the LION stock portfolio tracker ready and easy to use for everyone, it might contain bugs or not suit all the needs. In case of problems, having basic skills with Google Sheets, Google Data Studio, and programming skills with Google Apps Script will be a big help. Those skills are also necessary if you want to customize the spreadsheet, the script, or the dashboard of the LION stock portfolio tracker.

Getting started

Make a copy of the spreadsheet

  • Go to the spreadsheet in the demo section
  • From the spreadsheet's toolbar:
    • Click File menu
    • Click Make a copy menu item
  • In the copied spreadsheet, you will see the menu Lion Stock Portfolio Tracker on the spreadsheet's toolbar.
  • The script is available in the copied spreadsheet. It is transparent and you can verify it.
    • From the spreadsheet's toolbar:
      • Click Tools menu
      • Click Script editor menu item

Make a copy of the dashboard

  • Go to your Google Data Studio home page
  • From the Create button, create respectively 6 data sources to the 6 sheets of the copied spreadsheet: Transactions, Values, Benchmarks, Evolutions, Buy-Sell Evaluation, and Overview. You name the data source by the corresponding sheet's name.
  • Go to the dashboard in the demo section
  • From the dashboard's toolbar
    • Click Make a copy of this report button
    • A dialog will appear to ask for data sources. On the New Data Source side, you select the newly created data source that matches the one on the Original Data Source side.
    • Click Copy Report button

How to use

  • Set values for the parameters in the Configuration sheet, particularly for the Target Annual Rate and the Reference Index
  • Run the Delete Historical Prices Sheets function to delete all sheets for the stocks of the sample portfolio
  • In the Transactions sheet, replace sample transactions with your portfolio's transactions. You need to manually insert your transactions into this sheet or find a way to import them all at once.
  • In the Companies sheet, replace sample data with those of stocks that you have purchased
  • If you make a new transaction, add it to the Transactions sheet
  • If you buy new stock for the first time:
    • Update the Companies sheet for that new stock
    • Run the Generate Historical Prices Sheet function from the menu to create a new historical prices sheet for that new stock
    • Update the Values sheet for that new stock
  • If there are stock splits after your transactions, you need to update the number of shares for old transactions accordingly, because the historical prices provided by GOOGLEFINANCE take into account those splits.
  • Every day, to compute the portfolio's evolution with the latest stocks close prices:
    • Run Generate Evolutions Sheet function
    • Run Generate Buy-Sell Evaluation Sheet function
    • Run Generate Benchmarks Sheet function
    • You can schedule daily these executions with triggers
  • The dashboard in Google Data Studio is refreshed regularly to visualize the most updated data. During the trading time, there are some delays with GOOGLEFINANCE.
  • If you see something strange in data or charts, don't panic, take time to verify every sheet and try to debug the script.

Spreadsheet

The spreadsheet is used to register the stock portfolio's transactions. The stock portfolio's performance and evolution are computed and stored in the spreadsheet. This section explains how sheets are organized and the structure of each sheet.

Sheet Configuration

This sheet contains the settings of the stock portfolio tracker. The parameters that will be used by the script to compute other sheets. You can change the values of these parameters. NOTE: Do not change the names of these parameters!

Parameter Default value Description
Date Format dd/mm/yyyy The format that will be applied to all generated columns of type DATE in the spreadsheet
Number Format #,##0.000 The format that will be applied to all generated columns of type NUMBER in the spreadsheet
Font Family Inconsolata The font family that will be applied to all generated sheets
Unknown Symbols EPA:UG

The list of symbols separated by commas for stocks that have been purchased in your portfolio but are not recognizable by the GOOGLEFINANCE function. GOOGLEFINANCE does not provide the historical prices for these stocks. Instead, you need to manually provide the historical price for these stocks during at least the period that you hold these stocks in your portfolio. These sheets must have 2 columns Date and Close. The script will not generate or delete historical prices sheets for these stocks. If you don't provide the historical prices for these stocks, the script will not compute your portfolio's evolution during the period that these symbols were hold in your portfolio.

For example, I purchased the stock EPA:UG and had hold it for a while. The company has been merged with another company and its stock has changed to another name. I created manually the sheet EPA:UG and fill in manually the historical prices for EPA:UG. I then added EPA:UG to the list of Unknown Symbols.

Target Annual Rate 0.05

Saving is the simplest alternative to stock investment. This parameter is the annual interest rate of saving that you want to achieve if you use deposited money for saving instead of buying stocks. The parameter will be used to generate the Benchmarks sheet.

For example, I want my stock portfolio to grow about 5% a year, so I define here 0.05.

Reference Index INDEXEURO:PX1

It is the symbol of the market index that you want to compare your portfolio against. The symbol must be recognizable by the GOOGLEFINANCE function, otherwise, you need to provide manually its historical prices sheet and add the symbol to the list of Unknown Symbols above. The parameter will be used to generate the Benchmarks sheet.

For example, all my stocks are traded in the french stock market and I want to compare my stock portfolio with the french market index that is CAC40. So I define here INDEXEURO:PX1 that is the symbol of CAC40 and is recognizable by the GOOGLEFINANCE function.

Sheet Transactions

This sheet registers your stock portfolio's transactions. You add a new line in the sheet when you buy/sell shares of a company, deposit/withdraw money, or receive dividends. Transactions are essential inputs to the stock portfolio tracker and will be used to compute other sheets.

Column Type Description
Date Date Date when the transaction happened
Type Text

It can be one of the following values:

  • DEPOSIT: Money is added to the portfolio
  • BUY: Money in the portfolio is used to buy shares of a company
  • SELL: Money is added into the portfolio by selling shares of a company
  • DIVIDEND: Dividend money is added to the portfolio by owning a stock
  • WITHDRAWAL: Money is taken out of the portfolio
Symbol Text

It is the symbol of the stock involved in the transaction.

  • It is only applicable for BUY, SELL, and DIVIDEND transactions.
  • The cell is left empty for DEPOSIT and WITHDRAWAL transactions.
Amount Number

It is the money value of the transaction.

  • The amount is positive if the money goes into the portfolio, which is applicable for DEPOSIT, SELL and DIVIDEND transactions.
  • The amount is negative if the money goes out of the portfolio, which is applicable for BUY and WITHDRAWAL transactions.

*Note*: A transaction's amount is supposed to include fees if applicable.

Shares Number

It is the number of shares of the stock involved in the transaction.

  • It is only applicable for BUY, SELL and DIVIDEND transactions
  • The cell is left empty for DEPOSIT and WITHDRAWAL transactions.
  • The number of shares is positive if it is a BUY transaction or a DIVIDEND transaction.
  • The number of shares is negative if it is a SELL transaction.

*Note*: If there are stock splits after your transactions, you need to update the number of shares for old transactions accordingly, because the historical prices provided by GOOGLEFINANCE take into account those splits.

Change Formula

It is the difference between the transaction's amount and the current market value for the number of shares involved.

  • It is only applicable for BUY and SELL transactions.
  • If it is a BUY transaction and the change is positive, it means that the stock's price has increased since you bought it.
  • If it is a SELL transaction and the change is positive, it means that the stock's price has decreased since you sold it.
  • The cell is computed by a formula that you only need to apply to new transactions.

The formula for this column on row 2 is:

=IF(OR(B2="BUY",B2="SELL"),GOOGLEFINANCE(C2)*E2+D2,"")
Industry Formula

It is the industry of the stock involved in the transaction.

  • It is only applicable for BUY, SELL, and DIVIDEND transactions.
  • The cell is computed by a formula that you only need to apply to new transactions.

The formula for this column on row 2 is:

=IF(ISBLANK(C2),"",VLOOKUP(C2,Companies!A:D,3,FALSE))
Sector Formula

It is the sector of the stock involved in the transaction.

  • It is only applicable for BUY, SELL, and DIVIDEND transactions.
  • The cell is computed by a formula that you only need to apply to new transactions.

The formula for this column on row 2 is:

=IF(ISBLANK(C2),"",VLOOKUP(C2,Companies!A:D,4,FALSE))

Note: As a dividend can be paid in stock or in cash, to keep it simple and compatible with the transaction's structure above:

  • If a dividend is paid in stock, it can be represented as a BUY transaction with amount = 0 and shares > 0. It is considered as getting shares for free.
  • If a dividend is paid in cash, it can be represented as a DIVIDEND transaction with amount > 0, and shares > 0, but it does not mean more shares are added to the portfolio for this stock. It helps to know the amount of dividend for each share.

Sheet Companies

This sheet contains information about the Name, Industry, and Sector of stocks that you have purchased. The information will help to analyze your portfolio's diversification. You need to manually provide the information because they are not available through the GOOGLEFINANCE function.

Sheet Overview

This sheet contains the main indicators about your stock portfolio. These indicators are computed by applying basic formulas on the Transactions and Values sheets data. These indicators will be displayed as scorecards in the dashboard.

Column Description
Deposit

The total amount of DEPOSIT transactions

=SUMIF(Transactions!B:B,"DEPOSIT",Transactions!D:D)
Withdrawal

The total amount of WITHDRAWAL transactions

=SUMIF(Transactions!B:B,"WITHDRAWAL",Transactions!D:D)
Invested

The total amount of DEPOSIT and WITHDRAWAL transactions

=A2-B2
Cash

The total amount of all transactions

=SUM(Transactions!D:D)
Market Value

The total market value of currently holding stocks based on their latest close prices

=SUM(Values!O:O)
Portfolio Value

The sum of the market value and the available cash

=D2+E2
Dividend

The total amount of DIVIDEND transactions

=SUMIF(Transactions!B:B,"DIVIDEND",Transactions!D:D)
Yield

The ratio between the dividend amount and the invested amount

=G2/C2
Gain

The difference between the portfolio value amount and the invested amount

=F2-C2
Gain Percent

The ratio between the gain amount and the invested amount

=I2/C2
Last Change

The change of market value compared to the previous trading day's closes

=SUM(Values!T:T)
Last Change Percent

The ratio between the last change amount and the invested amount

=K2/C2

Sheet Values

This sheet computes the latest state for all stocks you have purchased. This sheet is the pivot table of the Transactions sheet. The first two columns Symbol and Shares are generated by the pivot table function. These other columns are computed with basic formulas. If you purchase a new stock for the first time, you need to update the sheet by dragging the formulas of columns on row 2 to other rows.

Column Description
Symbol The symbol of the stock, generated by the pivot table.
Shares The total number of shares of BUY and SELL transactions of the stock, computed by the pivot table.
Low52

The stock's 52-week low price, provided by GOOGLEFINANCE.

The formula for this column on row 2 is:

=GOOGLEFINANCE(A2, "low52")
Price

The stock's current price, provided by GOOGLEFINANCE.

The formula for this column on row 2 is:

=GOOGLEFINANCE(A2)
High 52

The stock's 52-week high price, provided by GOOGLEFINANCE.

The formula for this column on row 2 is:

=GOOGLEFINANCE(A2, "high52")
Change

The price change since the previous trading day's close, provided by GOOGLEFINANCE.

The formula for this column on row 2 is:

=GOOGLEFINANCE(A2, "change")
Change Percent

The percentage change in price since the previous trading day's close, provided by GOOGLEFINANCE.

The formula for this column on row 2 is:

=GOOGLEFINANCE(A2, "changepct")/100
P/E

The price/earnings ratio, provided by GOOGLEFINANCE.

The formula for this column on row 2 is:

=IFERROR(GOOGLEFINANCE(A2,"pe"),"")
Market Capitalization

The market capitalization of the stock, provided by GOOGLEFINANCE.

The formula for this column on row 2 is:

=GOOGLEFINANCE(A2, "marketcap")
Total Buy

The total amount of BUY transactions for the stock.

The formula for this column on row 2 is:

=SUMIFS(Transactions!D:D,Transactions!B:B,"BUY",Transactions!C:C,A2)
Total Sell

The total amount of SELL transactions for the stock.

The formula for this column on row 2 is:

=SUMIFS(Transactions!D:D,Transactions!B:B,"SELL",Transactions!C:C,A2)
Invested

The absolute value of the total buy amount.

The formula for this column on row 2 is:

=ABS(J2)
Current Cost

The sum of the total buy amount and the total sell amount for the stock. It is only applicable for stocks whose number of shares in your portfolio is greater than 0.

The formula for this column on row 2 is:

=IF(B2>0,ABS(J2+K2),"")
Current Unit Cost

The cost per share for the stock. It is only applicable for stocks whose number of shares in your portfolio is greater than 0.

The formula for this column on row 2 is:

=IF(B2>0,M2/B2,"")
Market Value

The market value for the stock.

The formula for this column on row 2 is:

=IF(B2>0,B2*D2,0)
Dividend

The total dividend amount received by owning the stock.

The formula for this column on row 2 is:

=SUMIFS(Transactions!D:D,Transactions!B:B,"DIVIDEND",Transactions!C:C,A2)
Yield

The ratio between the dividend amount and the invested amount for the stock.

The formula for this column on row 2 is:

=P2/L2
Gain

The sum of the total buy amount, the total sell amount and the current market value for the stock.

The formula for this column on row 2 is:

=J2+K2+O2
Gain Percent

The ratio between the gain amount and the invested amount for the stock.

The formula for this column on row 2 is:

=R2/L2
Total Last Change

The last change for all holding shares of the stock.

The formula for this column on row 2 is:

=IF(B2>0,F2*B2,0)
Last Change Percent

The percentage change in price since the previous trading day's close.

The formula for this column on row 2 is:

=G2
Name

The name of the stock.

The formula for this column on row 2 is:

=VLOOKUP(A2,Companies!A:D,2,FALSE)
Industry

The industry of the stock.

The formula for this column on row 2 is:

=VLOOKUP(A2,Companies!A:D,3,FALSE)
Sector

The sector of the stock.

The formula for this column on row 2 is:

=VLOOKUP(A2,Companies!A:D,4,FALSE)

Sheet Evolutions

This sheet contains data about the portfolio's evolution from the first transaction date until today. The sheet is computed and generated by the script's function Generate Evolutions Sheet.

Column Description
Date Starting from the first transaction date until today.
Invested The total amount of money has been invested until the date. It is the sum of amount of DEPOSIT and WITHDRAWAL transactions that have been made until the date.
Cash The available money in the portfolio on the date. It is the sum of amount of all transactions that have been made until the date.
Market Value The total market value for all holding stocks in the portfolio on the date based on their close prices on the date. From the transactions that have been made until the date, the script computes the number of shares for stocks in the portfolio and then multiply with their close prices on the date.
Portfolio Value The sum of the cash amount and market value amount on the date.
Gain The difference between the portfolio value and the invested amount on the date.
Gain Percent The ratio between the gain amount and the invested amount on the date.

Sheet Buy-Sell Evaluation

This sheet contains data that helps to evaluate the efficiency of each BUY and SELL transaction for a stock. The sheet aggregates all historical prices sheets of all stocks presented in the portfolio. The unit amount for each share of stock in BUY and SELL transactions is presented along with its historical prices. The idea is to evaluate if a BUY or SELL transaction was made at the right moment and to make improvements for future transactions.

Column Description
Symbol The symbol of stock presented in the portfolio.
Date Each stock has its own date range that starts from the first transaction date until today.
Close The stock's close price on that date.
Unit Cost The stock's unit cost if it is still being held in the portfolio on that date. It is computed by divide the total amount of BUY and SELL transactions of the stock until that date by the number of shares of the stock in the portfolio on that date.
Unit Buy The unit amount for each share of the stock if a BUY transaction was made on that date
Unit Sell The unit amount for each share of the stock if a SELL transaction was made on that date
Dividend Per Share The unit amount for each share of the stock if a DIVIDEND transaction was made on that date.
Value The market value for holding shares of the stock based on its close price on the date.
Cost The total amount of BUY and SELL transactions for the stock until the date if the stock was still being held in the portfolio on that date

Sheet Benchmarks

This sheet compares the stock portfolio's evolution with 2 alternative investments:

  • One alternative investment is to put money in a saving account with compound interest. The annual saving interest rate is defined in the Configuration sheet. The idea is that the amount of a DEPOSIT transaction today, for example, will generate daily interest from tomorrow. And the interest generated for today will generate interest for tomorrow. For each day from the first transaction date until today, the script computes the Saving Interest, Saving Balance, Saving Gain, and Saving Gain Percent on that date.
  • Another alternative investment is to invest only in a market index. The Reference Index is defined in the Configuration sheet. The idea is that the close price of the chosen index on the first transaction date is used as the reference price. For each day from the first transaction date until today, the script computes the change in percent of the market index's close price on that date with the reference price on the first transaction date.
Column Description
Date Starting from the first transaction date until today
Cashflow The total amount of DEPOSIT and WITHDRAWAL transactions made ON the date
Invested The total amount of DEPOSIT and WITHDRAWAL transactions have been made UNTIL the date
Saving Interest The daily interest generated with the saving balance of the previous day
Saving Balance The sum of the saving balance on the previous day, the cashflow of the date and the saving interest of the date
Saving Gain The total cumulative saving interest until the date
Saving Gain Percent The ratio between the saving gain and the invested
Portfolio Value The sum of the portfolio's available cash and its holding stocks' market value on the date
Portfolio Gain The difference between the portfolio value and the invested amount in the portfolio on the date
Portfolio Gain Percent The ratio between the portfolio gain and the invested amount in the portfolio on the date
Reference Index Price The reference market index's close price on the date
Reference Index Change Percent The change in percent of the market index's close price on that date with its close price on the first transaction date

Sheets Historical Prices of Stocks

Each stock presented in the portfolio has its own sheet containing its historical prices from the first transaction date of the portfolio until today. A sheet's name is a stock's symbol. Each sheet contains only 2 columns that are Date and Close.

For example, the symbol EPA:ENGI involved in the sample portfolio's transactions, its historical prices sheet is necessary for the LION scripts. A sheet named EPA:ENGI is generated. The formula =GOOGLEFINANCE("EPA:ENGI", "price", DATE(2017,12,27), TODAY(), "DAILY") is set on the cell A2. The formula creates 2 columns that are Date and Close.

Theses sheets are generated by the function Generate Historical Prices Sheets below. These sheets are hidden by default and it is possible to show/hide them with the functions Show Historical Prices Sheets and Hide Historical Prices Sheets.

Menu Item Description
Generate Historical Prices Sheets This function will generate for each symbol found in the Transactions sheet a sheet that contains the stock's historical prices from the first transaction date until today. The sheet's name is the stock's symbol. If you buy a stock for the first time, you need to run this function.
Delete Historical Prices Sheets This function will delete existing historical prices sheets for all symbols found in the Transactions sheet. You can run this function to reinitialize the spreadsheet if you think something doesn't work as expected.
Show Historical Prices Sheets By default, historical prices sheets are hidden. If you want to verify those sheets, you can run this function to show them. You can also see them in the menu View -> Hidden Sheets
Hide Historical Prices Sheets This function will hide historical prices sheets for all symbols found in the Transactions sheet.
Update Values Sheet If you buy a new stock for the first time, the pivot table adds a new line in the Values sheet. You can run this function to apply formulas to the new line in the Values sheet.
Generate Evolutions Sheet This function uses the Transactions sheet and all historical prices sheet to compute the portfolio's evolution from the first transaction date until today. The data is then written to the Evolutions sheet.
Generate Buy-Sell Evaluation Sheet This function uses the Transactions sheet and all historical prices sheet to compute data for the Buy-Sell Evaluation Sheet.
Generate Benchmarks Sheet This function uses the values of Target Annual Rate and Reference Index parameter that are defined in the Configuration sheet to compute data for the Benchmarks Sheet. The Benchmarks Sheet helps to compare your stock portfolio with 2 alternative investments. One alternative investment is to invest in a market index. Another alternative investment is to save money with a target annual compound interest rate.
Clear All Caches The parameters in the Configuration sheet are cached to help improving the performance of scripts. If you change the values of these parameters, you need to run this function firstly so that the next executions of others functions will use the new values of these parameters.

Triggers

To keep track closely the portfolio's evolution, you need to run the 3 functions Generate Evolutions Sheet, Generate Buy-Sell Evaluation Sheet, and Generate Benchmarks Sheet every day. You can automate these tasks by scheduling them with triggers. You can create 3 triggers for the 3 functions generateEvolutionsSheet, generateBuySellEvaluationSheet, and generateBenchmarksSheet that will execute them every day at about 7 am before the market's opening.

  • Go to the copied spreadsheet
  • Go to the script editor
    • From the spreadsheet's toolbar:
      • Click Tools menu
      • Click Script editor menu item
  • Select the menu Triggers on the left side panel of the script editor
  • Click the button + Add Trigger
  • Choose generateEvolutionsSheet for the select Choose which function to run
  • Choose Time-driven for the select Select event source
  • Choose Day timer for the select Select type of time based trigger
  • Choose 6am to 7am for the select Select time of day
  • Save the trigger
  • Create two other triggers for generateBuySellEvaluationSheet and generateBenchmarksSheet

Dashboard

The dashboard is built with Google Data Studio. It has 5 pages: Overview, Diversification, Dividend, Buy-Sell Evaluations, and Benchmarks. The data to visualize are fetched from the above sheets via a built-in connector of Google Data Studio. This section explains how each page of the dashboard is designed and how to use them to track the stock portfolio.

Data sources

The dashboard of LION stock portfolio trackers needs 6 data sources corresponding to 6 sheets: Transactions, Values, Benchmarks, Evolutions, Buy-Sell Evaluation, and Overview. Each page of the dashboard contains many charts and each chart uses data from one data source.

Page Overview

This page shows an overview about the stock portfolio.

Page Overview of the stock portfolio tracker's dashboard in Google Data Studio
Chart Description
Scorecards

It displays the indicators computed in the sheet Overview:

  • All values are presented in compact numbers.
  • The scorecards Gain, Gain Percent, Last Change, Last Change Percent are in green color if the values are positive, and in red color if the values are negative.
Invested / Portfolio Value / Gain time series Present the portfolio's evolution since the first transaction date. It uses the Evolutions sheet's data
Invested and Dividend time series
  • The Invested chart presents the evolution of the Invested amount since the first transaction date and the Invested amount each year.
  • The Dividend chart presents the evolution of the Dividend amount since the first transaction date and the Dividend amount each year.

The 2 charts use Transactions sheet's data.

Diversification pie chart Present the distribution of market value by industry. It uses the Values sheet's data.
Transactions table

Present the Transactions sheet's data.

Values table Present the Values sheet's data.

Page Diversification

This page shows the diversification of the stock portfolio by industry, by sector, and by stock.

Page Diversification of the stock portfolio tracker's dashboard in Google Data Studio
Column Description
Avg P/E The average P/E of the portfolio's stocks that are under the industry or the sector
Market Value The total market value of the portfolio's stocks that are under the industry or the sector
Market Value Part The percentage of the market value for the industry, the sector or the stock compared to the portfolio's total market value
Dividend The total dividend of the portfolio's stocks that are under the industry or the sector
Dividend Part The percentage of the dividend for the industry, the sector or the stock compared to the portfolio's total dividend
Gain The total gain of the portfolio's stocks that are under the industry or the sector

Page Benchmarks

This page compares the stock portfolio with 2 alternative investments: a saving account with compound interest and a reference market index. The table below explains briefly what data are visualized on this page. For a more detailed explanation of the 2 benchmarks, you can read these two posts:

Page Benchmarks of the stock portfolio tracker's dashboard in Google Data Studio
Chart Description
Invested / Portfolio Value / Saving Balance time series chart

It compares the evolutions of the 3 values since the first transaction date.

  • The Saving Balance line is always above the Invested line.
  • If the Portfolio Value line is above the Invested line, the stock portfolio is generating money. Otherwise, it loses money.
  • If the Portfolio Value line is above the Saving Balance, the stock portfolio is outperforming the saving account.
Portfolio Gain / Saving Gain time series chart

It compares the evolutions of the 2 values since the first transaction date.

  • The Saving Gain line is always above the 0 line and it increases gradually.
  • If the Portfolio Gain line is above the Saving Gain line, the stock portfolio is outperforming the saving account.
Portfolio Gain Percent / Saving Gain Percent / Reference Index Change Percent time series chart

It compares the evolutions of the 3 values since the first transaction date.

  • The Portfolio Gain Percent is the ratio between the gain on a date and the invested amount on that date.
  • The Saving Gain Percent is the ratio between the total interest amount accumulated until a date and the invested amount until that date.
  • The Reference Index Change Percent is the ratio between the index's close price on a date and the index's close price on the first transaction date.

Page Dividend

Dividend income tracker with Google Sheets and Google Data Studio

This page helps to track the dividend income of the stock portfolio.

Chart Description
Dividend scorecards
  • One scorecard presents the total accumulated dividend until today.
  • One scorecard presents the yield that is the ratio between the total accumulated dividend and the total invested amount.
Dividend time series chart The chart compares the dividend income between this year and the last year. On the chart, you should hope to see the dividend income of this year is higher than last year.
Dividend by year and by month table The heat map table presents the distribution of dividends on each month and each year. It helps to manage the regularity of receiving dividends.
Dividend by year and by industry / symbol The heat map table presents the distribution of dividends of each industry/symbol and each year. It helps to manage the diversification of dividend income.

Page Buy-Sell Evaluation

This page helps to evaluate the buy and sell transactions for the portfolio's stocks.

Page Buy-Sell Evaluation of the stock portfolio tracker's dashboard in Google Data Studio
Chart Description
Symbol selector This selector allows selecting only one symbol at a time among all symbols presented in the portfolio.
Date range selector This selector allows focusing on a specific period instead of the default period, which is started from the first transaction date until today.
Indicators table The table presents main indicators about the symbol. These indicators are extracted from the Values sheet.
Trending charts The two time series chart present the trending of the selected symbol and the portfolio gain during the last 90 days. The charts help to evaluate the recent correlation between the selected symbol and the portfolio. The charts are not controlled by the date range selector above.
Close / Unit Cost / Unit Buy / Unit Sell / Dividend Per Share time series chart This chart presents the BUY, SELL, and DIVIDEND transactions along with the historical prices of the selected symbol. It helps to evaluate whether a BUY or SELL transaction was made at the right moment. The chart starts from the first transaction date until today. It uses the BUY-SELL Evaluation sheet's data.
Value / Cost time series chart This chart presents the evolutions of the market value and the cost for the selected symbol. If the Value line is above the Cost line, the investment on the selected symbol is making money, otherwise, it is losing money. The chart starts from the first transaction date until today. It uses the BUY-SELL Evaluation sheet's data.
Transactions table This table presents the transactions of the selected symbol. By default, the transactions are sorted by date descending.

FAQ

getHistoricalPricesBySymbol - TypeError: Cannot read property 'getRange' of null

TypeError: Cannot read property 'getRange' of null

It seems like the historical prices sheet for a symbol is not yet correctly generated.

  • Verify whether all symbols in your transactions have their historical prices sheets generated. Normally, those sheets are hidden by default. Please refer to these 2 sections Sheets Historical Prices of Stocks and Menu Lion Stock Portfolio Tracker
  • Otherwise, you can add a breakpoint at the beginning of function getHistoricalPricesBySymbol(symbol) and find out which symbol is having problem.

What to do in case a stock changes its symbol?

If a stock changes symbol, GOOGLEFINANCE function will only return data for the new symbol and not return any data to the old symbol. Consequently, if you had made transactions of that stock before the symbol change, you need to update the Symbol of those transactions into the new symbol.

For example, Total company (EPA:FP) has recently renamed itself as TotalEnergies (EPA:TTE). As the result, I need to update all transactions of EPA:FP to EPA:TTE.

What to do in case of a stock split?

If a split happens to a stock, GOOGLEFINANCE function will reflect that split to the historical prices of that stock. Consequently, if you had made transactions of that stock before the split, you need to update the Shares of those transactions in accordance to the split.

For example, I had bought 1 share of Dassault Aviation company (EPA:AM) and received 1 dividend payment before the company announced a split ten-for-one on October, 1st 2021. As the result, I need to update previous transactions related to the EPA:AM stock by changing the number of shares from 1 to 10.

Before the ten-for-one split:

Date Type Symbol Amount Shares
18/05/2021 DIVIDEND EPA:AM 12.30 1
13/10/2020 BUY EPA:AM -744.92 1

After the ten-for-one split:

Date Type Symbol Amount Shares
18/05/2021 DIVIDEND EPA:AM 12.30 10
13/10/2020 BUY EPA:AM -744.92 10

Feedback

I hope that you have succeeded in building your personal stock portfolio tracker with Google Sheets and Google Data Studio by following this guide. If you have any feedback, question, or request about this stock portfolio tracker you can contact me via the contact form on this blog or write me an email to allstacksdeveloper@gmail.com.

Disclaimer

The post and the LION stock portfolio tracker are only for informational purposes and not for trading purposes or financial advice. It is your responsibility to use the LION stock portfolio tracker for managing your investment. I shall not be liable for any damages relating to your use of the LION stock portfolio tracker.

Comments

  1. Hi,

    it looks great! Thank you! It is working great with different stocks, but what about ETF?
    They have a lot of industries and sectors in one ETF. How is it possible to see a nice diversification chart all over stocks and ETF's ? Country diversification chart would be also very nice.

    ReplyDelete
    Replies
    1. Hi Mike,

      If you can fetch data about the industries and sectors of ETFs, it's totally possible to create a nice diversification chart for both stocks and ETFs. However, it might require many efforts to accomplish.

      Thank you for the suggestion about diversification by geography. I'll look into it.

      Delete
  2. When using your sheet, I get an error "TypeError: aDate.getFullYear is not a function" when generating the sheet. This happens in the Evolutions and the Buy-Sell Evaluations sheet. The Benchmark sheet works fine.
    At first it worked fine, however after I have removed your transcations and companies and replaced them with a couple of my own I now get this error.
    Can you assist ?

    ReplyDelete
    Replies
    1. I doubt that some dates that you enter in the Transactions tab are not recognized as type Date.

      You can double click on any date that you entered in the column Date of the Transactions tab, you should expect to see a calendar popup that allows you to select a date.

      If the calendar popup does not appear, that means your input is not a valid Date type.

      Delete
  3. Hi,

    I'm having trouble with the data sources in my copied report.

    After making a copy of the LION Stock Portfolio Tracker - Spreadsheet as described, I went to the LION Stock Portfolio Tracker - Dashboard and followed the steps to click to make a copy of the Dashboard report.

    A dialog did appear to ask for data sources and, on the New Data Source side there were drop-down options matching each of the spreadsheet tabs, but on the Original Data Source side each one showed as "Unknown", so I wasn't sure what I was matching.

    I tried to match them up as per the list in the docs (i.e. Transactions, Values, Benchmarks, Evolutions, Buy-Sell Evaluation, and Overview) and this worked for some sections of the report (e.g. the Diversification section), but most parts showed an error.

    Any suggestions as to what I might be doing wrong?
    Thanks.

    ReplyDelete
    Replies
    1. Hi,

      Thank you for your feed back. Yes, you're right about the "Unknown" on the Original Data Source side. I also see that if I use a different Google account to make a copy. I look into it to improve the guide.

      Can you try to make a another copy, and select your sources in the order (top to bottom): Overview, Evolutions, Values, Transactions, Benchmarks, Buy-Sell Evaluation?

      Thanks.

      Delete
    2. Fantastic, selecting the data sources in the order you suggested worked!

      Thanks for your help.

      Delete
    3. That's great! Thanks again for your feedback.

      Delete

Post a Comment

Popular posts from this blog

Create personal stock portfolio tracker with Google Sheets and Google Data Studio

I have been investing in the stock market for a while. I was looking for a software tool that could help me better manage my portfolio, but, could not find one that satisfied my needs. One day, I discovered that the Google Sheets application has a built-in function called GOOGLEFINANCE which fetches current or historical prices of stocks into spreadsheets. So I thought it is totally possible to build my own personal portfolio tracker with Google Sheets. I can register my transactions in a sheet and use the pivot table, built-in functions such as GOOGLEFINANCE, and Apps Script to automate the computation for daily evolutions of my portfolio as well as the current position for each stock in my portfolio. I then drew some sort of charts within the spreadsheet to have some visual ideas of my portfolio. However, I quickly found it inconvenient to have the charts overlapped the table and to switch back and forth among sheets in the spreadsheet. That's when I came to know the existen

Stock Correlation Analysis With Google Sheets

Correlation is a statistical relationship that measures how related the movement of one variable is compared to another variable. For example, stock prices fluctuate over time and are correlated accordingly or inversely to one another. Understanding stock correlation and being able to perform analysis are very helpful in managing a stock portfolio investment. In this post, we will look at how to perform stock correlation analysis with Google Sheets. Understanding correlation and its applications in stock investing Stock correlation analysis with Google Sheets Getting started User guide Conclusion Disclaimer Understanding correlation and its applications in stock investing The most familiar correlation measure is the Pearson product-moment correlation coefficient . The strength of the relationship between two variables is expressed numerically between -1 and 1. For example: Two stocks are positively correlated when their prices always go up or go down together. Their co

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 Dat

Create a dividend income tracker with Google Sheets by simply using pivot tables

As my investment strategy is to buy stocks that pay regular and stable dividends during a long-term period, I need to monitor my dividends income by stocks, by months, and by years, so that I can answer quickly and exactly the following questions: How much dividend did I receive on a given month and a given year? How much dividend did I receive for a given stock in a given year? Have a given stock's annual dividend per share kept increasing gradually over years? Have a given stock's annual dividend yield been stable over years? In this post, I explain how to create a dividend tracker with Google Sheets. Manage stock transactions with Google Sheets Create dividend tracker with Google Sheets Track annual dividend amount of stocks Track dividend amount by month and by year Track annual dividend per share of stocks Track annual dividend yield of stocks Demo Conclusion Buy me a coffee Feedback References Disclaimer Manage stock transactions with Go