# 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](https://goldprice.dev/downloads/gold-portfolio-tracker-google-sheets.xlsx). 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.

| Column | What to enter |
| --- | --- |
| Holding | A label such as `1 oz bar` or `18K ring` |
| Unit | `grams` or `troy oz` |
| Quantity | The measured quantity |
| Purity | `99.9%` for a 24K-style bar, `75%` for 18K, or another documented purity |
| Purchase cost | Your total purchase cost in the same currency as Price Data |
| Purchase date | The date you bought or recorded the holding |
| Notes | Optional dealer, receipt, or identification note |

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](https://goldprice.dev/downloads/gold-portfolio-tracker-google-sheets.gs), 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](https://goldprice.dev/tutorials/google-sheets). This portfolio workbook is the next step: it turns a reference price into a holding-by-holding, timestamped calculation.