This automated Google Sheet spreadsheet uses Google Finance functions and scrapes select data from FINVIZ and Yahoo Finance. This sheet also makes use of Script Editor to gives users the ability to track any transactions within a brokerage account. It then populates this data automatically into the "Positions" tab. This data is further broken down and summarized in the "Breakdown" tab. Support for international security investments (outside the U.S.) are also supported, with a currency conversion tab and functionalities (see video below for demonstration).
Note: The only thing you have to input are the details on column A, B, C, D, and E on the "Transactions" tab. EVERYTHING else automatically populates, or is updated through a drop-down functionality!
Clear ALL example transaction entries (do NOT clear the last "Total" column). Then, type in the date of the transaction, the ticker/security traded, the transaction type, the quantity bought (fractional shares included), and the price per share. The "Total" column populates automatically.
Supports 998 transactions, but more can easily be added if necessary (just add more rows and drag down all formulas).
Supported transactions include (see example and video for details):
Deposit: Cash deposited in your investment account (put 1 for Quantity and amount for Price).
Buy: When you purchase a security. Input share quantity purchased and price the traded executed.
Sell: When you sell a security. Input share quantity sold and price the traded executed. Ensure you do NOT sell more shares than you bought at a previous date.
Interest: Any interest earned from your investments (put 1 for Quantity and amount for Price).
Adjustment: Examples include earnings adjustments, potential realized and unrealized gains/losses, fees/expenses for a particular fund. In most cases, you will not use this option!
SPLIT: Whether the stock price split its shares (i.e., write "2:1" under Quantity for a 2-for-1 stock split, meaning every shareholder will receive an additional share for each share held). Do NOT enter in anything under Price! The "Total" column should show "-" instead.
DRIP: Stands for dividend reinvestment plan. If this is enabled in your investment account and a dividend is reinvested, you can enter in the DRIP amount (i.e., 0.07) and the stock ticker's current stock price under Price.
Dividend: Alternative to DRIP. If this is enabled in your investment account and a dividend is reinvested, you can enter in 1 for Quantity and the dividend amount for Price.
Withdrawal: Cash withdrawn from your investment account (put 1 for Quantity and amount for Price).
On the "Positions" tab:
Nothing, everything automatically updates. This can take time! Try refreshing or closing other Google Sheet tabs if loading is slow.
Supports 100 assets in your portfolio, but more can easily be added if necessary (just add more rows and drag down all formulas).
On the "Currency Conversion" tab:
Use the drop-down in the top left of the sheet to select the currency you want the entire Google Sheet to display. This is only an option if you have a security in the "Transactions" tab in that specific currency (e.g., only "USD" will display if the portfolio only consists of U.S. securities).
Again, supports 100 assets in your portfolio, but more can easily be added if necessary (just add more rows and drag down all formulas).
The appropriate purchase price, current price, change, cost, value, and gain/loss will be shown based on the currency selected from the drop-down.
This is then displayed on the "Transactions" tab on the last two columns and on the "Breakdown" tab accordingly.
Note: Go to "More Formats" (displayed as "123" as a drop-down), then "More Currencies" to change "$" (USD) to currency of choosing, such as "€" (Euro).
On the "Breakdown" tab:
Markets: Displays 6 price, percentage, and chart details on popular market indices. Displays current time and whether the market is closed/open (based on NYSE/NASDAQ trading hours).
Portfolio Balances/Stats: 12 statistics/measures to help you understand your portfolios overall and daily performance.
Overall portfolio performance is displayed on a stacked column chart.
Portfolio value in cash and with security positions displayed on a pie chart.
Note: Biggest Gainer/Loser based on daily performance, not overall.
Industry Breakdown: Provides a broad overview of how much industry exposure you have to particular industries. Includes pie chart.
Bottom of Sheet: Select a ticker/security from the black drop-down box. This is based on your current positions. Everything else automatically updates, including basic security details, a description of the security, and 12-month time-series stock price chart.
To avoid problems on Transactions tab:
Do NOT enter in a Sell for more than the Buy/Drip quantity.
Do NOT enter in a Sell if you have NOT previously entered a Buy/Drip.
ALWAYS organize transactions by date in chronological order.
Do NOT enter in negative numbers whatsoever (i.e., a "-" before a number)!
NEVER alter columns F, G, and H on the Transactions tab nor any other cells on the other sheets (unless you know what you're doing)!
Positions tab explained:
Security: Based on the security/ticker you entered in Transactions.
Shares Owned: The exact number of shares owned.
Avg. Price: The weighted average price paid.
Industry: The broad industry, from FINVIZ.
Current Price: Current stock price.
Cost Basis: The total amount paid, based on the shares owned and avg. price.
Current Value: The current value of the shares owned, based on current price and shares owned.
Change (% / Amount): Percent and value gain/loss return on a daily basis.
Gain/Loss (% / Amount): Percent and value gain/loss return based on current price and avg. price (for %), and current value and cost basis (for $).
Dividend Yield: Current dividend yield, from FINVIZ.
Annual Dividend: Annual dividend paid, from FINVIZ.
Annual Div. Income: Annual dividend income expected, based on shares owned and annual dividend.
Dividend formulas you can copy/paste (not on the sheet currently):
Payout Ratio: The dividend payout ratio, from FINVIZ: =IFERROR(SUBSTITUTE(INDEX(IMPORTHTML("https://finviz.com/quote.ashx?t="&A3,"table",8),11,8),"*","")*1,"-")
Yield on Cost: The rate of dividend income your original investment earns today, based on annual dividend and avg. price: =IFERROR((L3/C3),"-")
Ex-Dividend Date: When the next ex-dividend date occurs. This is when the stock begins trading without the subsequent dividend value: =IF(ISBLANK(A3),"",INDEX(IMPORTHTML(CONCATENATE("https://finance.yahoo.com/quote/",A3),"table",2),7,2))
Breakdown tab explained:
Number of Buy/Sell Trades: Total amount of Buy/Sell trades in the Transactions tab.
Total Cost Basis: The total amount paid for all positions within the portfolio, based on the total shares owned and average prices.
Total Market Value: The total value of all shares owned within the portfolio, based on the current value of all shares and shares owned.
One-Day Return: The one-day return of your portfolio.
Unrealized Gain/Loss: The unrealized gain/loss you would realize if you sold all your positions now.
Realized Gain/Loss: The realized gain/loss, based on all the shares you sold in the Transactions tab.
Annual Dividend Income: The total annual dividend income expected if all of your dividend-paying companies paid out their annual dividend, based on the total shares owned and annual dividend amount.
Broken down by security/ticker on the "Annual Dividend Breakdown" bar chart.
Positions: Total value of all of your positions (in the appropriate currency)
Cash Value: Total amount of cash within your portfolio.
Account Value: Your total account value, sum of "Positions" and "Cash Value."
Biggest Gainer: The best performing security in your portfolio over the trading day, displays security and percentage.
Biggest Loser: The worst performing security in your portfolio over the trading day, displays security and percentage.
Industry Exposure: The broad industry exposure in the portfolio, based on the securities displayed in the Positions tab.
Market Value: The total market value of shares owned within a particular industry.
Pie chart visualizing industry breakdown/exposure within portfolio
Individual Security Information
As mentioned above, select a ticker/security from the black drop-down box. This is based on your current positions. Everything else will automatically update.
Includes: stock price, market cap, change (% and $), 52-week high/low, beta, P/E ratio, and EPS.
Shows ticker/security name and 12-month stock price performance
Note: Scripts can take time to load depending on size of the spreadsheet, where data is being sourced from, the number of active Google Sheets spreadsheets you have open, and Google servers...