Build a Gold Portfolio Tracker in Google Sheets

If you hold bars, coins, or jewelry, a single total is hard to audit. This workbook gives each holding its own row, keeps purchase cost separate from current spot value, and shows the timestamp used for the price. It is a calculation aid for indicative valuation, not an appraisal or a promise of what a dealer will pay.

Download and import the workbook

Download the gold portfolio tracker workbook. In Google Drive, choose New → File upload, open the uploaded .xlsx file, then choose File → Save as Google Sheets. Google’s help page documents opening and downloading spreadsheet files in Drive: https://support.google.com/docs/answer/49114.

The workbook has three sheets:

• Portfolio is where you enter holdings and read the summary.
• Price Data holds the currency, endpoint, timestamp, and 24K price used by the formulas.
• Read me explains the assumptions and the limits of the estimate.

The sheet names and column names stay in English so formulas, screenshots, and support instructions match the downloadable file.

Enter holdings

On Portfolio, replace the three example rows with your own data. One row is one holding or one group of identical items.

Use the same currency for every purchase cost. The workbook converts troy ounces using 31.1034768 grams and leaves the original quantity visible, so you can check the conversion.

Update the reference price

The API endpoint used by the template is:

GET https://api.goldprice.dev/v1/carat?currency=USD

The response includes price_gram_24k, a timestamp, and the response currency. Copy the numeric 24K price into Price Data → B6, the response timestamp into B5, and the matching quote currency into B7. Update all three cells together. Change B3 only when you have a new quote for that currency, and make sure your purchase costs use the same currency. The endpoint’s price fields are decimal strings in JSON; entering the numeric value in the sheet lets spreadsheet formulas calculate with it. Do not relabel an old USD quote as IDR by changing B3 alone.

The download contains clearly marked example data so the formulas have a visible result immediately after import. Replace the example price before using the result. The workbook does not fetch the API by itself and stores no API key. If a shared workbook needs authenticated access, use a server-side proxy; do not put a credential in this spreadsheet or in a bound script that other editors can inspect.

Optional one-click refresh

If you want a manual refresh menu, download the companion Apps Script, open the workbook in Google Sheets, choose Extensions → Apps Script, replace the starter code, and paste the file contents. Save and reload the spreadsheet. The Gold portfolio → Refresh price data menu then makes one keyless request using the currency in Price Data → B3. It validates the HTTP status, response currency, positive finite price_gram_24k, and timestamp before writing B5:B7 together. A failed request leaves the previous price, timestamp, and quote currency unchanged. If you paste manually, update B5, B6, and B7 from the same response. The script creates no timer and does not poll in the background.

How the calculation works

Each row uses four transparent steps:

1. Weight (g) converts the quantity to grams.
2. Spot price/g links to the 24K price in Price Data.
3. Indicative value is Weight (g) × Purity × Spot price/g.
4. Change is Indicative value − Purchase cost.

For example, 10 grams at 75% purity and a 24K price of 128.24 per gram gives 10 × 0.75 × 128.24 = 961.80 in the price currency. This is the value of the gold content at the reference spot price. It does not include the jewelry’s workmanship, stones, taxes, dealer spread, local premium, or a buyback discount.

The summary counts populated quantities, totals converted weight, adds purchase costs, and adds row-level indicative values. A blank row stays blank in the calculation columns instead of appearing as a zero holding.

Keep the timestamp with the number

Spot prices change. The Price timestamp (UTC) in the summary tells you which response the formulas use. When you refresh the price, update the timestamp at the same time. If you record the workbook for a report, keep a copy of the source response or write its timestamp into your notes so another person can reproduce the calculation.

Common mistakes

• Entering 0.999 as text instead of entering 99.9% as a percentage.
• Mixing USD purchase costs with an IDR reference price.
• Treating a gram as a troy ounce. Select troy oz only when the quantity is actually in troy ounces.
• Reading the Change column as a guaranteed profit. It is a comparison with the reference spot value and ignores selling costs.
• Leaving the example price in place after adding real holdings.

Limits of the estimate

This workbook is useful for organizing holdings and checking the arithmetic behind a spot-based estimate. It is not an appraisal, accounting record, tax statement, investment recommendation, or guaranteed sale price. A dealer quote can differ because of spreads, premiums, refining costs, local taxes, condition, and the terms of the transaction. Check local rules and the actual quote before making a financial decision.

Related guide

For live and historical gold prices in a sheet, see the Gold Price API for Google Sheets tutorial. This portfolio workbook is the next step: it turns a reference price into a holding-by-holding, timestamped calculation.