Accounts
Overview of the Accounts Sheets
Last updated
Overview of the Accounts Sheets
Last updated
Accounts are the building blocks of your portfolio. Each one is independent and self-contained.
If you wanted to keep things simple, you could use only the account sheets, or even just a single account sheet, and you would still get a fully functional portfolio tracker replete with metrics and a ROI chart!
Here's an overview of how accounts work:
Each account can either be a crypto account or a stocks account. If you enter crypto trades into a stocks account (or vice-versa), the automatically generated Holdings table won't be able to properly recognize the asset codes you enter into the Trade Log.
You define the account type by choosing from the pop-up menu at the very top of each account's page, in cell B1.
At the very top of the sheet, you can see your portfolio's current stats, such as:
your portfolio's overall P&L in USD terms
your portfolio's overall P&L in % terms
its current value
it's cost (the capital you have put in minus the capital you have taken out)
your realized P&L
the current unrealized P&L on the assets currently held in the account.
If you have selected a secondary currency on the Dashboard, additional stats in that currency will show up on the rightmost column of the Top Line, and the currency you have selected will be displayed in C1.
Tip: The Realized P&L is likely to be the amount you should keep an eye on for taxes!
The only cell you should edit here is the account's type (B1).
Right below the Top Line Stats is the Account's ROI Chart. This chart works in tandem with the Snapshots table to plot your % ROI over time.
To help you see how your account is performing, the chart also plots 3 benchmarks ( the % ROIs of Bitcoin, Ethereum, and the S&P 500).
You can quickly enable / disable benchmarks by clicking on the checkbox underneath the name of the respective benchmarks in the chart's legend (see screenshot below).
The starting date for this ROI calculation is set when you enter your very first snapshot in the Snapshots table. Each time you take a new snapshot, a data point is added to the ROI Chart. New data points are likewise added for each benchmark. Over time, you can start seeing your account's performance plotted against those of these benchmarks.
Right below the ROI chart, you'll find the account's Holdings table.
This table is entirely auto-generated. It will appear rowless until you begin entering trades in the Trade Log. A new row will be automatically added for each new asset you trade in one of your accounts. It will then pull live price data from Yahoo Finance and CoinMarketCap in order to give you rich metrics about your account:
Ticker (The asset codes you've entered in the Trade Log)
Asset's name. You can click these to be brought to the Asset's page on Yahoo Finance.
Current Holdings (number of units currently owned for each asset).
Daily % Change in price of the asset.
Current Price of the asset.
Cost / Unit (The total cost of your current holdings of the asset divided by the number of units currently owned. This is your current cost basis per unit.
The price at which you last transacted this asset.
Whether your last transaction of this asset was a BUY or a SELL.
The current value of your holdings of this asset.
The total cost of your current holdings for the asset (similar to 6.)
The current $ amount gains or losses on your current holdings of the asset.
The current % gains or losses on your current holdings of the asset.
Current R on your current holdings for this asset.
The $ amount of realized gains or losses for this asset. This is especially relevant for tax purposes.
The $ amount of realized gains or losses for this asset calculated in the secondary currency of your choice (if any). These amounts take into account the conversion rate on the day of each of your realized events so as to be as precise as possible. This is especially relevant for tax purposes.
The Total $ Cost for this asset: (cost of current holdings plus or minus any realized gains or losses for this asset)
The Total $ Gains for this asset: The current unrealized gains or losses plus the realized gains or losses for this asset.
The % share of your portfolio this asset represents.
Tip: Clickable asset names. You can now click on an asset' name in your Holdings, Portfolio Breakdown, Watchlists, etc. Doing so will bring you to the asset's page on Yahoo Finance where you can see live advanced charts (line, candles, bars), relevant news, and much more. This works for all supported assets, and respect your selections in special assignations if you have made any.
While crypto price data is continually refreshed throughout the day, stocks prices are usually the most recent "close" price, i.e., the price of the asset at the end of the last trading day. Sadly, this is a current limitation imposed by Yahoo's own API. Hopefully continual live price updates will be forthcoming.
Right below the Holdings table is the Trade Log.
The Trade Log is composed of 4 sections:
The first section is where you enter your trades (lighter cells).
The second section automatically displays useful updated stats about your holdings, reflecting the trade you just added to your Trade Log.
The third section displays the P&L VS Current price and the final P&L of each realized event (aka a SELL entry in the trade log), basically telling you how much money you made or loss on the trade.
The fourth section is for optional comments on your trade.
You only need to enter data in the first section of the Trade Log. The other other 2 main sections will self-populate to reflect the trade you just entered.
The first 7 columns (the lighter ones) are where you enter your trades. This is self-explanatory, but let's go over the details.
Date: the date of the trade. This column is pre-formatted to use a universal standard.
Please note that the date should be precise enough to allow for chronological sorting of your trades. In some cases, the time of the trade , in addition to its date, will be necessary (e.g., if you are both buying and selling the same asset on the same day).
Tip: A quick way to enter the exact date and time is via the application's menu.
Go to Table > Insert Current Time (this will enter both the date and the time down to the second).
Even quicker: hit Control - Shift - Command - T
If you are batch-importing your trades via the smol Formatter, the correctly formatted date and time will carry over.
Account: This is for optional metadata (E.g., Binance, Robinhood, 0xacB69, TFSA)
Side: BUY or SELL (or FEES, see box below).
Market: The asset code (ticker) of the asset traded.
Quantity: The number of units bought or sold.
Price: The USD price the asset was traded at.
Fees: Optionally, any fees incurred for that trade. The fees you enter here will be added to your cost basis for that specific asset and will be reflected in your final P&L.
In addition to "BUY" and "SELL", the Side column offers you a third option, "FEES". The reason is that, in crypto, there are often network fees to pay even when we are not buying or selling. Each time we interact with the blockchain, we must pay a gas fee. These fees impact your final P&L. The FEES option allows you to log these fees and add them to the cost basis of the asset concerned in the blockchain interaction.
The Stats section maintains important values updated with each event, such as the updated total of units owned for the asset as well as its updated cost basis. Many will find these columns informative and useful, but they can also be hidden if you don't have any use for this information. Do not delete them though.
The third section of the Trade Log computes your P&L.
The first column (% P&L VS Current Price) tells you in % terms how much higher or lower the current price is vis a vis the price transacted at (for each row / transaction). If the row registers a SELL event, the calculation is inverted, so that if the currenrt price is higher than the price you sold at, you will see a negative % P&L.
The second column ($ P&L VS Current Price) uses that percentage and multiplies it by the $amount transacted, letting you know in $ terms how much you may have gained or lost. N.B: These columns are purely for informational use and do not affect in any way the calculation of your account's P&L.
Each time you SELL an asset you own, the third and fourth columns display your final realized P&L both in % terms and in $ terms (proceeds of units sold minus cost of units sold). These figures also takes into account any fees you may have entered for this asset, both in the Fees column (when entering a BUY or SELL event), or via the FEES entry (see box above).
If you set a secondary currency, your P&L will also be displayed in that secondary currency in the fifth column, taking the exchange rate in effect on the date you have entered in column A.
You can sort your trades by header columns. The main utility of this is the ability to add trades to to the Trade Log and not have to worry about entering them in perfect chronological order. You can simply add trades to the bottom of the list and then sort the list by date, and everything will then get computed correctly.
Note that the Trade Log computes trades from top to bottom, so trades MUST be sorted chronologically to be correctly computed. For example, you obviously can't sell something you have not already bought. But even if you do not enter them chronologically, you can simply sort by date and everything will recompute accordingly so long as the dates you have entered are correct.
A good use case might be if you are using more than one exchange to trade Bitcoin, but want still want all these trades computed as one (say, for tax purposes). You want to know your P&L and cost basis for Bitcoin across all Exchanges, not just on one exchange. Simply enter all your trades in the Trade Log and then sort by date and your costs basis and P&L will be correctly computed across all exchanges, wallets, etc. (Use the Smol Formatter if you want to bulk import trades).
The Snapshots table captures important values about your account at different moments in time. These temporal data points are then used to track and display your account's performance through time. The Account's ROI Chart plots data from this table. So do the different charts of the ROI tool and the Portfolio Value Chart on the Dashboard.
When you first set up your Portfolio Tracker, you create a first "snapshot" of your account's value and cost, a baseline, with an ROI of 0. As the value of your holdings change, so does your account's value of course. Furthermore, when you add capital to, or retrieve capital from, the account, the cost of your account change. These changes are reflected in your account's return on investment or ROI. The snapshots are a record of your account's past performance.
To make things as simple as possible, the current values (updated with current asset prices) are continually displayed in bright blue in the box labeled "Current Values". To make a snapshot, simply copy these values (in blue), and paste them below, in the table using Shift + ⌘+ V (or Edit > Paste Formula Results).
Immediately, your Account's ROI Chart will display this first snapshot. As you add snapshots over time, the chart will begin to draw a smoothed blue line representing your account's performance over time.
