Build Your Own Portfolio Tracker | Crypto, Stocks, & ETFs All In One Place!

Name: Build Your Own Portfolio Tracker | Crypto, Stocks, & ETFs All In One Place!
Uploaded: 2022-02-26T13:42:08.000Z
Duration: 1 h 52 min

Introduction and Overview

The speaker introduces a comprehensive investment tracking tool that allows users to monitor their investments from multiple platforms in one place. The tool provides a summary of overall portfolio performance, individual investment analysis, portfolio location, dividends, and other insightful statistics.

Features of the Investment Tracker

The transactions tab is where users enter data on the movement of their investments, including cash deposits, buy and sell orders, cross-platform transfers, cash dividends, reinvestment plans, crypto interest, and cash withdrawals. This feature helps with investment decisions and tax audits.

The portfolio tab provides a summary of each investment with up-to-date statistics such as key performance indicators (KPIs), benchmark comparisons, actual versus target weights, asset class details, and sector information.

The dashboard offers an overview of the portfolio through dynamic charts and various screenos. It includes tables showing top performers and worst performers, a snapshot of account value, an investment screener with classic trendy charts for screening current or potential investments, and an individual performance screener to analyze buy versus close price orders.

The tracker also features colorful pie charts that visualize investment allocation per market value, asset class breakdowns per sector and market value. There is also an income breakdown section to track crypto interest earned and dividend income based on the chosen year.

A platform breakdown section separates calculations per platform to reconcile cash balances and identify activity levels on different platforms. Additionally, there are granular statistics on crypto holdings per platform for deeper analysis.

The entire tracker can be converted to any currency of choice, ensuring precision and consistency when dealing with multiple currencies. This feature helps calculate overall gains accurately.

The investment tracker is available in different themes such as light, dark, matrix, and cyberpunk. A mobile tab provides the same insights in a smartphone interface.

Tutorial on Using the Investment Tracker

Users can access a ready-to-use version of the tracker by visiting the speaker's Patreon page (linked in the video description). For those who want to learn how to build it step by step, a tutorial will be provided.

To enter transactions, users need to make a copy of the protected version of the tracker into their private account. They can then remove pre-populated transactions and add their own examples. Transactions include cash deposits, stock purchases in different currencies, buying ETFs from other countries using specific platforms, and purchasing cryptocurrencies from platforms like Binance. Asset names can be found on Coin Market Cap website for accurate tracking. -

The portfolio tab automatically updates with added transactions and displays all investments along with target weights set by the user. The dashboard also reflects these changes and provides breakdowns per platform and crypto holdings per platform. -

Users are shown how to transfer assets between platforms using the example of transferring half of Ethereum from Binance to BlockFi. This involves entering transfer details for both platforms in the transactions tab and observing changes in the crypto breakdown section on the dashboard. -

The tutorial continues beyond the given transcript.

Setting up the Transactions Tab

In this section, the speaker explains how to set up the Transactions tab in a spreadsheet.

Entering Column Headings

Merge and center the title.

Enter column headings exactly as shown in the video.

Wrapping Text and Centering

Select row 2 and wrap text.

Center align the text in the middle.

Adding Dummy Transactions

Enter some dummy transactions to see immediate results when entering formulas later.

Follow along with the speaker's sequence or enter your own dummy transactions.

Adding Calendar to Date Column

Select from row 3 to bottom of date column.

Click on Data > Data Validation.

Set criteria as "date" to enable a calendar for easy date entry.

Formatting Date Format

Select all cells again.

Go to More Formats > Choose desired date format.

Formulas for Total Column

This section covers the formula used for calculating totals in the Total column of the Transactions tab.

Using IF Statements and IFERROR

The formula uses a combination of IF statements and IFERROR functions.

Depending on whether it's a sell or cash withdrawal transaction, it multiplies quantity by price and subtracts fees.

For other transactions, it only multiplies quantity by price.

Creating Dropdown Lists

This section explains how to create dropdown lists for asset class, sector, and action columns in the Transactions tab.

Asset Class Dropdown List

Select all cells under Asset Class column.

Go to Data > Data Validation > List of items.

Enter desired asset classes or use pre-defined list from demo (recommended).

Sector Dropdown List

Pause video and copy provided sectors or add your own if needed.

Follow same steps as above but for the Sector column.

Action Dropdown List

Enter all operations as provided by the speaker.

Ensure correct spelling and formatting to link with formulas.

Rolling Costs Formulas

This section covers the formulas used to calculate rolling costs for each asset in the Transactions tab.

Calculating Rolling Cost

The formula scans all transactions and calculates the rolling cost of each asset.

It uses both total cost and rolling quantity to determine how the cost changes with each buy or sell transaction.

Currency Conversion

This section explains how to set up currency conversion in the Transactions tab.

Linking Currency Selection to Dashboard

Name Sheet1 as "Dashboard".

In cell L1, enter any currency (e.g., USD).

In Transactions tab, link cell Q2 to Dashboard!L1 using =Dashboard!L1 (fix with F4).

In cell R2, enter formula ="'"&Q2&"'" to display selected currency as title.

Converting Fees and Total Column

Use Google Finance function combined with IF statements and IFERROR.

Converts fees based on selected currency in Dashboard.

Total column formula analyzes transactions and entered currency.

Date Breakdown Columns

In this section, the speaker explains how to capture and break down dates in a spreadsheet. They demonstrate creating drop-down cells for selecting specific days, months, or years using formulas.

Creating Date Breakdown Columns

Use formulas to capture and break down dates into day, month, and year.

Create drop-down cells for selecting specific days, months, or years.

This technique is useful for creating trackers and analyzing data with query functions.

Formatting the Transactions Tab

The speaker demonstrates formatting the transactions tab by adding borders, colors, and conditional formatting. They explain how to use conditional formatting rules to highlight specific text values and format rows based on certain conditions.

Formatting the Transactions Tab

Add colors to column headers for visual appeal.

Apply conditional formatting to highlight specific text values (e.g., "cache" in blue).

Use custom formulas in conditional formatting rules to format rows based on cell values.

Format totals column with gray background and bold font.

Make empty cells consistent by setting them as light gray or white.

Remove grid lines for a cleaner look.

Add dotted borders at the bottom of each cell in the transaction section.

Freeze top rows (headers) to keep them visible while scrolling.

Working on the Portfolio Tab

The speaker explains that they will now work on the portfolio tab. They mention combining the investment portfolio and currency conversion sections into one tab. They instruct adding additional rows and entering column headers for various currencies.

Adding Rows and Column Headers

Insert rows up to column BC for future use.

Combine investment portfolio and currency conversion sections into one tab.

Enter 55 different types of column headers related to currencies (pause video if needed).

Copy all headers except "Currency" and paste them next to it for direct numbers.

Wrap text, center, and bold the headers in size 12 font.

Tickers and Currency Conversion

The speaker explains the importance of tickers for automating formulas. They introduce the "unique" and "filter" formulas to exclude cash asset names from tickers. They also mention using currency conversion for accurate calculations.

Tickers and Currency Conversion

Use the "unique" and "filter" formulas to extract tickers from transactions.

Apply size 11 font to ticker values.

Utilize a formula to determine the currency associated with each ticker.

Note that the portfolio may not be accurate if multiple currencies are involved.

Explain the purpose of currency conversion for consistent value representation.

The summary has been created based on the provided transcript.

Copying and Dragging Formulas with Keyboard Shortcuts

In this section, the speaker explains how to copy and drag formulas using keyboard shortcuts in order to save time.

Copying and Dragging Formulas

To copy a formula, select it and press Command/Control + C.

Navigate to the desired column (right or left) and go to the bottom.

Return to the original cell and press Command/Control + Shift + Up Arrow to jump back to the first formula.

Press Command/Control + Enter to drag down the formula across all selected cells.

Using VLOOKUP Function for Asset Names

The speaker demonstrates how to use a simple VLOOKUP function to retrieve asset names for entered cryptocurrencies.

Retrieving Asset Names with VLOOKUP

Use a VLOOKUP function to find asset names for each cryptocurrency.

If no match is found, leave the cell blank.

Asset names are essential for scanning current crypto prices.

Quantity Formula Explanation

The speaker explains the complex quantity formula used in the spreadsheet. This formula calculates current quantities based on various conditions such as buying, selling, transferring, etc.

Understanding Quantity Formula

The quantity formula is long but effective.

It utilizes SUMIFS with different conditions for adding up quantities and subtracting them.

Different conditions include buy/sell type, stock/ETF type, stable coins, etc.

Specifying asset class is necessary when dealing with stable coins.

The formula handles scenarios where an asset was bought and then sold.

Average Price Calculation Formula

The speaker discusses how average price calculation is done using a VLOOKUP function with rolling costs obtained from transactions.

Calculating Average Price

Use a VLOOKUP function with rolling costs from transactions to calculate the average price.

This formula finds the current price of the entered ticket using Google Finance or web scraping if necessary.

Formatting and Sparkline Function

The speaker explains how to format cells for displaying prices and introduces the sparkline function for visualizing price movements.

Formatting and Sparkline Function

Enter "365" in cell AH2 and change its format using custom number formatting.

Apply a custom number format to display day trends dynamically based on user input.

Use the sparkline function to create bar charts showing price movement based on the number of days entered.

Daily Percentage Change Calculation

The speaker demonstrates how to calculate daily percentage change using the change percentage formula. This formula provides a dollar value of how much the investment has increased or decreased within one day.

Calculating Daily Percentage Change

Utilize the change percentage formula to calculate daily percentage change.

Multiply the change by quantity to obtain a real dollar value of investment changes.

Cost, Current Value, and Unrealized Return Formulas

The speaker explains formulas for calculating cost, current value, and unrealized return on investment.

Cost, Current Value, and Unrealized Return Formulas

Multiply quantity by average purchase price to calculate cost.

Multiply quantity by current price to determine current value.

Use a formula combining current value and cost for calculating unrealized return on investment.

Realized Profit and Loss Formula Explanation

The speaker discusses the realized profit and loss formula used in scanning transactions. This formula adds up sold amounts, dividends, subtracts buy amounts, dividend reinvestment, etc., providing an estimate of realized gains or losses.

Understanding Realized Profit and Loss Formula

The realized profit and loss formula scans transactions for sold amounts, dividends, buy amounts, dividend reinvestment, etc.

It adds up stablecoin column to exclude stablecoin purchases from realized gains.

Note that the realized profit and loss formula is not exact due to changing costs based on quantity and average price.

Performance Metrics: Benchmarks

The speaker introduces benchmarks in the spreadsheet and explains how to calculate day return, benchmark return, weight, and target weight.

Calculating Benchmarks

Merge cells for day return and benchmark columns.

Use formulas to calculate the change in price between now and 365 days ago for both ticker and benchmark.

Calculate the difference between ticker return and benchmark return.

Determine weight by dividing the current value of each ticker by the sum of all other values.

Link target weight to a separate column for manual input in converted currency portfolio.

Asset Class Calculation

The speaker demonstrates how to determine asset class using a VLOOKUP function with an asset class identifier.

Determining Asset Class

Use a VLOOKUP function with an asset class identifier to determine asset class.

Add up all quantities based on asset class identifier.

Portfolio Tab - Original Currencies

In this section, the speaker explains how to create the portfolio tab in the original currencies. They mention using a VLOOKUP function to get the sector for each asset and applying formulas to convert currencies.

Creating the Portfolio Tab

Use a VLOOKUP function with transactions to get the sector for each asset.

Copy the right section of the table and apply formulas to convert currencies.

Link asset name, ticker, and quantity columns to the right side of the table.

Use a formula to calculate average purchase price based on currency conversion.

Stable coins won't be converted when changing dashboard currency.

Apply similar formulas for current price and sparkling columns.

Target Weight Column

The speaker explains how to set up the target weight column in the portfolio tab. They demonstrate linking it to asset class, calculating actual weight using a formula, and linking sector as well.

Setting Up Target Weight Column

Change title of target weight column using a formula that captures entered percentages.

Link asset class column to corresponding column on the right side.

Use a formula to find actual weight for each asset class.

Link sector column again from right side.

Formatting and Conditional Formatting

The speaker demonstrates formatting techniques applied to the portfolio tab. They show adding colors, borders, and conditional formatting rules based on values in different columns.

Formatting and Conditional Formatting

Add colors and borders to column headers for visual appeal.

Apply conditional formatting rules based on value comparisons (greater than zero = green, less than zero = red).

Format target weight column cells as blue if not empty (manual entry).

Group original currencies table for better organization.

Conclusion

The speaker concludes the portfolio tab section and mentions formatting changes made to the sheet. They also mention grouping columns and leaving the original currencies table for reference purposes.

Conclusion

Blank spaces in original currencies section are filled when linked to target weight.

Formatting changes include colors, borders, and conditional formatting.

Original currencies table is left for reference and can be expanded if needed.

Columns are grouped for better organization.

Portfolio Tab

In this section, the speaker explains how to create a filtered portfolio tab by making a copy of the original portfolio tab and adding filters to it.

Creating Filtered Portfolio Tab

Make a copy of the original portfolio tab.

Link the filtered portfolio tab to the first cell of the main portfolio.

Drag the link across all necessary cells.

Format the copied table to match the original portfolio.

Delete any unnecessary columns in the filtered portfolio.

Dashboard Setup

The speaker discusses setting up tables, charts, and graphs for a dashboard.

Currency Dropdown

Enter "Currency" as a title in cell K1.

Go to Data > Data Validation > List and enter desired currencies for dropdown selection.

Overall Portfolio Snapshot Table

Merge cells B3 and C3 for title.

Format table titles and center align everything.

Merge cells D3 and E3 for market value column title.

Use formulas to calculate total portfolio return, current return, daily change, and percentage change.

Top Performer of Today

Merge cells B8 and C8 as well as D8 and E8.

Copy merged cells into line 10.

Use offset, match, and max functions in cell B9 to determine top performer based on daily change.

Working on the Account Balances Table

In this section, we will focus on the account balances table and perform various calculations.

Merging Cells and Formatting

Merge cells B13 and C13.

Apply formatting to the merged cell.

Drag the formatting down to row 19.

Calculating Account Value

The account value is calculated by adding positions and cash.

Currently, the account value is zero.

Fill in the positions and cash values.

Using SUMIF Formulas for Cash Calculation

Use a combination of SUMIF formulas to calculate cash.

Add up cash deposits, sales, dividends, and subtract cash withdrawals.

Include stable coins purchased.

Calculating Compounded Annual Growth Rate (CAGR)

Use the CAGR formula: market value divided by cost raised to the power of 1 divided by the number of years trading.

Utilize the YEARFRAC function for precise calculations when trading for less than a year.

Adjust the formula for longer trading periods if needed.

Working on the Statistics Table

In this section, we will work on creating a dynamic statistics table.

Merging Cells and Formatting

Merge cells B and C in row 22.

Apply formatting to the merged cell.

Drag the formatting down to row 35.

Setting Start and End Dates

Select cells D22 and D23 for start and end dates respectively.

Apply data validation with date format for easy selection.

For end date, use TODAY() formula or manually select from calendar.

Using SUMIFS Formulas with Date Criteria

Most formulas in this table use SUMIFS function based on selected dates.

Capture dates from transactions column (column C).

Ensure transactions fall within selected date range.

Counting Transactions and Approximate Dividend Yield

Use COUNTIFS formula to count the number of transactions within selected dates.

Calculate approximate dividend yield by dividing total dividend income by portfolio market value.

Working on the Screener

In this section, we will focus on creating a screener for investments.

Merging Cells and Formatting

Merge cells N2 and N3.

Apply formatting to the merged cell.

Drag the formatting across columns N to T.

Merge cells N4 and O4.

Apply formatting to the merged cell.

Drag the formatting down to row 17.

Setting Ticker Range for Data Validation

Select cell P3 (or P2 and P3) for data validation settings.

Go to Data > Data Validation > Select range from portfolio's ticker column (remove row).

This creates a dropdown with owned investments.

Using Google Finance Formulas for Investment Information

Enter formulas using Google Finance attributes:

Name: Retrieves investment name for each row.

Signal: Scans 52-week high, low, and current price to determine buy, sell, or hold signal.

Adding Sparkline Function for Trendy Chart

Use sparkline function to create dynamic trendy chart based on price changes over time.

Red indicates price decrease compared to previous period.

Green indicates price increase compared to previous period.

Individual Performance Screener

In this section, we will work on an individual performance screener.

Copying Data Validation Cell

Copy cell P2 and P3 from the screener section.

Paste it in the individual performance screener section.

Using QUERY Function for Lookup

Utilize QUERY function with ticker selection as criteria in column A.

Retrieve desired values from column I based on selected ticker.

Adjust percentages for buy and sell factors in the signal formula according to your investment strategy.

Setting Up Performance Tracking

In this section, the speaker explains how to set up performance tracking for a specific asset using Google Sheets.

Getting Dates and Buy Prices

Use cell W20 to get the dates of when the asset was bought and the buy price.

Utilize the Google Finance function to retrieve the close price based on the purchase date.

Using Filter Formula

Use the filter formula to scan for transaction dates in column C and select dates that match the ticker entered in column D.

Apply additional conditions to only choose transactions with "buy" as the type.

Repeat the same formula for column J to get the buy price.

Querying Historical Prices

Combine query and Google Finance functions to obtain a list of historical prices.

Select one single price based on a chosen date.

Income Breakdown Tables

This section focuses on creating income breakdown tables in Google Sheets.

Year Screener

In cell O36, set up data validation by selecting a range from transactions (column V).

Choose all years entered in transactions by selecting column V from V3 to bottom.

List of Months Based on Year

Use unique and query functions to select months (column U) where column V contains the chosen year.

Cash Dividends and Dividend Reinvestment

Utilize two sumifs functions to calculate cash dividends and dividend reinvestment based on selected year.

Drag down formulas until row 48.

Crypto Interest Earned

Use unique and filter functions with multiple conditions to retrieve crypto interest received.

Apply sumifs formula for calculating actual quantity of interest received.

Drag down formulas until row 49.

Platform Breakdown

This section explains how to create a platform breakdown table in Google Sheets.

Sorting and Unique Functions

Use sort and unique functions to obtain a list of platforms entered in transactions (column A).

Calculating Statistics for Each Platform

Apply different formulas for each header to scan through the transactions and retrieve relevant information.

Drag down formulas until row 62.

Portfolio per Cryptocurrency

In this section, the speaker demonstrates how to create a portfolio per cryptocurrency table in Google Sheets.

Listing Cryptocurrencies and Stable Coins

Enter the asset names (not tickers) in cell B64.

The purpose is to summarize all holdings per cryptocurrency or stable coin.

Totaling Values for Each Cryptocurrency

Calculate total values for each cryptocurrency by summing up holdings across different currencies.

Drag down formulas as needed.

There are no more tables underneath.

Performance Indicators for Platform Breakdown

The speaker explains why they excluded performance indicators for the platform breakdown in their analysis. They mention that formulas like index match or vlookup wouldn't be able to show multiple platforms for the same asset. Instead, they use easily obtainable indicators from transactions.

Excluded performance indicators for platform breakdown due to limitations of formulas like index match or vlookup.

Multiple platforms for the same asset cannot be accurately represented using these formulas.

Instead, focus on using indicators obtained from transactions.

Third Generation Tracker and Performance Indicators

The speaker expresses hope for a third generation tracker that would provide more performance indicators per platform. They emphasize the importance of accuracy and precision in providing these indicators.

Hopeful about a future third generation tracker with more performance indicators per platform.

Emphasize the need for accuracy and precision in providing these indicators.

Currently working on improving and expanding the analysis.

Crypto Breakdown per Platform

The speaker introduces a dynamic table that shows the breakdown of cryptocurrencies per platform. This table automatically updates when new platforms or cryptocurrencies are added.

Introduce a dynamic table showing crypto breakdown per platform.

Uses transpose, unique, and filter functions to populate the table.

Table automatically updates when new platforms or cryptocurrencies are added.

Formatting and Customization of Tables

The speaker discusses formatting and customization options for tables. They explain how to add borders, colors, conditional formatting, and make tables dynamic and automatic.

Format tables by adding borders, colors, conditional formatting.

Use conditional formatting to highlight positive/negative values or specific signals (buy/sell/hold).

Utilize color scales to differentiate values based on magnitude.

Make tables dynamic and automatic by using rules such as "cell is not empty".

Creating Charts

The speaker demonstrates how to create various charts based on the data. They explain the steps to create column, combo, pie, and line charts using different data ranges.

Create column chart to visualize percentage gain/loss of investments.

Create combo chart to show current value and cost of investments.

Create pie charts for asset class breakdown, cash vs. positions, sector breakdown.

Create line chart for individual performance screener based on buy price and close price.

Conclusion

The speaker concludes the tutorial by summarizing the content covered in the video. They mention that the created charts are fully dynamic and based on user-selected tickers and transaction data.

Recap of the content covered in the tutorial.

Emphasize that created charts are fully dynamic based on user-selected tickers and transaction data.

Timestamps have been associated with relevant bullet points as per instructions.

New Section

This section provides an overview of the portfolio tracker second generation and its features.

Portfolio Tracker Second Generation

When hiding columns in the portfolio tracker, a message will appear reminding users to click "include data" to ensure that the chart still includes data from those hidden rows.

The speaker concludes by hoping that viewers enjoyed the video and find the portfolio tracker helpful.