By the CryptoTrackPro team · Published
Quick answer: multiply each holding by its current price, divide by the total for its portfolio weight, then compare that weight with your own target. Use =cryptotrackpro("BTC-USD") for Bitcoin's price. The sheet calculates allocation gaps; it does not place trades.
A portfolio can move away from the percentages you originally recorded even when you leave the holdings untouched. This tutorial adds an allocation review to a Google Sheets crypto tracker. You supply the holdings and target percentages. The formulas show which positions are above or below those targets in both currency and units.
The targets and prices below are invented to demonstrate the arithmetic. They are not suggested allocations or current market quotes. Choose a single reporting currency for the entire calculation, and check the quantities against your own records before interpreting the result.
1. Set up the input columns
Install CryptoTrackPro, then choose Extensions → CryptoTrackPro → Open CryptoTrackPro to connect. Create an empty tab called Allocation. Put Symbol in A1, Units in B1, Price USD in C1, Value USD in D1, Target weight in E1, Current weight in F1, Target value in G1, Value gap in H1, and Units gap in I1.
| Row | A: Symbol | B: Units | C: Example price | E: Target weight |
|---|---|---|---|---|
| 2 | BTC | 0.02 | 60,000 | 50% |
| 3 | ETH | 0.4 | 3,000 | 30% |
| 4 | SOL | 4 | 150 | 20% |
Enter the example prices manually first if you want to reproduce every result below. Once the arithmetic is working, replace C2 with the following formula and copy it down through C4:
=cryptotrackpro(A2&"-USD")Changing a coin in column A changes the requested pair. If you prefer EUR, change the formula suffix to "-EUR" for every row and relabel the currency columns. Mixing USD and EUR values in the same total produces a meaningless allocation.
2. Calculate value and current weight
In D2 enter =B2*C2 and copy down to D4. In D5, calculate the portfolio total only when every price is numeric:
=IF(COUNT(C2:C4)=ROWS(C2:C4),SUM(D2:D4),NA())This guard matters because an add-on can return a message when a symbol cannot be priced or a daily limit is reached. Treating a missing price as zero would make the remaining positions appear larger than they are. Read the original price cell when the total is unavailable.
Enter =D2/$D$5 in F2 and fill down. Format E2:F4 as percentages. The dollar signs keep the total fixed when the formula moves between rows. With the example prices, the values are $1,200, $1,200, and $600: a $3,000 total with weights of 40%, 40%, and 20%.
3. Show the gap to each target
First put =SUM(E2:E4) in E5 and confirm it equals 100%. A target total above or below 100% means the model is incomplete. Cash or other holdings need explicit rows if they belong in the allocation you are measuring.
| Cell | Formula | Meaning |
|---|---|---|
| G2 | =E2*$D$5 | Value corresponding to your target |
| H2 | =G2-D2 | Positive means below target value |
| I2 | =H2/C2 | Gap expressed in asset units |
The example produces a BTC value gap of +$300, an ETH gap of −$300, and a SOL gap of $0. Dividing by the illustrative prices gives +0.005 BTC and −0.1 ETH. These are model differences before fees, spreads, minimum order sizes, or changes in price; they are not executable order instructions.
4. Make the review easy to read
Add a column chart comparing E2:E4 with F2:F4, using A2:A4 as the asset labels. Name the series Target weight and Current weight. Keep H and I visible alongside the chart so a percentage difference can be checked against its currency amount.
For a periodic record, copy the results into a separate dated tab and paste values only. A historical snapshot should preserve the values you reviewed, rather than keep recalculating with today's price cells. Record the reporting currency and the time you ran the refresh beside the snapshot.
Refresh before reviewing the allocation
Use Extensions → CryptoTrackPro → Refresh All Prices before the review. Formula prices are not a continuous streaming feed. Base supports timed updates every 30 minutes and Pro every 5 minutes while both the spreadsheet and sidebar stay open. Closing the sidebar stops those timed updates.
The Free plan includes 50 calls per day. Base is $3.99 per month with 500 daily calls; Pro is $11.99 per month with unlimited daily calls. First-time users receive a 30-day Base trial without a card and move to Free unless they subscribe. See plan details and the setup guide for the refresh controls.
If your main question is performance rather than allocation, use the separate Bitcoin benchmark tutorial. Allocation describes how a portfolio is divided at one moment; a performance comparison measures how its value changed over a defined period.
Start with a working portfolio sheet
Copy the free template, install CryptoTrackPro, and adapt the layout to your workflow. First-time users get a 30-day Base trial with no card required.
Get the free portfolio template