This automated Google Sheet spreadsheet uses Google Finance functions and scrapes select data from FINVIZ, Fidelity, and Yahoo Finance. This sheet also makes use of Script Editor to gives users the ability to track any 'Buy' and 'Sell' transactions in their portfolio on the "Transactions" tab, along with 'DRIP' for any reinvested dividends. It then populates this data automatically into the "Positions" tab. This data is further broken down and summarized in the "Breakdown" tab.
How to use:
To edit spreadsheet: FILE → MAKE A COPY
On the "Transactions" tab:
Clear ALL example entries (do NOT clear the last "Total" column). Then, type in the date of the transaction, the ticker/security traded, whether it was a Buy/Sell/Drip, the quantity bought (fractional shares included), and the price per share. The "Total" column populates automatically.
On the "Positions" tab:
Nothing, everything automatically updates. This can take time!
On the "Breakdown" tab:
Select a ticker/security from the black drop-down box. This is based on your current positions. Everything else automatically updates.
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.
Organize transactions by date in chronological order.
Do not enter in negative numbers for Sell.
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 Fidelity.
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.
Gain/Loss (% / $): Percent and U.S dollar 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.
Payout Ratio: The dividend payout ratio, from FINVIZ.
Yield on Cost: The rate of dividend income your original investment earns today, based on annual dividend and avg. price.
Annual Div. Income: Annual dividend income expected, based on shares owned and annual dividend.
Ex-Dividend Date: When the next ex-dividend date occurs. This is when the stock begins trading without the subsequent dividend value.
Breakdown tab explained:
Number of Buy/Sell Trades: Total amount of Buy/Sell in the Transactions tab.
Number of Transactions: Total amount of transactions in the Transactions tab (includes DRIPs).
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.
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.
Industry Exposure: The broad industry exposure in the portfolio, based on the securities in the Positions tab.
Market Value: The total market value of shares owned within the respective 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...