keepcalculator

06 Numbers and Powers

Track Costs Across Multiple AI APIs Without a Backend

Card No. 06·77937 · Updated · Standard formula, shown below

Track spending across several AI APIs without building a server by recording usage in a local spreadsheet and applying each provider’s published pricing. You will need usage details from each provider, a spreadsheet app, and a way to keep API keys out of browser code. The result is a practical estimate and audit trail—not a replacement for providers’ billing dashboards.

What You’ll Need

  • A spreadsheet saved locally or in a private cloud account, such as a workbook in a spreadsheet application.
  • Read-only access to usage or billing reports from each API provider. Exact report fields and export options vary.
  • Current pricing information for the models and features you used, including input, output, cached-token, image, audio, or tool charges when applicable.
  • A consistent record of request date, provider, model, token or unit counts, and any relevant feature usage.
  • A plan for collecting usage that does not expose secret API keys in a web page, browser extension, or shared spreadsheet.

Step-by-Step

  1. Choose a safe source for usage data. Start with provider dashboards, downloadable usage reports, or billing exports. If your application already logs request usage, use those records only if they contain counts and model identifiers—not API secrets or sensitive prompt text. Never put a secret key in front-end JavaScript: visitors can inspect browser code and network requests. A no-backend workflow means you manually import or enter usage; it does not make browser-held credentials safe.
  2. Create a usage ledger. Add columns for date, provider, project or account, model, request count, input tokens, output tokens, cached input tokens, other billable units, currency, source, and notes. Include only columns relevant to your providers. Keep one row per model and billing period, or one row per usage export line. Avoid mixing daily totals with monthly totals in the same row.
  3. Make a separate pricing table. Record provider, exact model or pricing category, effective date, input price per unit, output price per unit, cached-input price if applicable, and other applicable rates. Note the unit shown in the provider’s pricing documentation—often a price per million tokens, but not always. Preserve old rates rather than overwriting them; pricing can change, and historical usage should be calculated using the rate that applied at the time.
  4. Normalize units before calculating. If a rate is per million tokens, divide the relevant token count by 1,000,000 before multiplying by that rate. If a rate is per thousand, divide by 1,000 instead. Keep token counts as numbers, not text with commas or labels. For image, audio, or other usage, use the provider’s stated billing unit and conversion rules; do not assume every image or audio request has a fixed token cost.
  5. Calculate line-item estimates. For a simple text request, use: estimated cost = (input tokens ÷ pricing unit × input rate) + (output tokens ÷ pricing unit × output rate). Add cached-token charges or other billable components separately, following the provider’s definitions so the same tokens are not counted twice. In the spreadsheet, use a lookup from provider, model, and effective date to the matching pricing row. If you cannot confidently match a rate, flag the row instead of silently using a different model’s price.
  6. Check the provider’s billing definitions. “Input” may include more than the text you typed, such as conversation history, system instructions, or tool-related content. Some models report cached tokens separately; others have additional charges for tools or modalities. Use the usage fields and price categories provided for that specific model. Record the source and date you checked the pricing page.
  7. Import reports consistently. When downloading a CSV, retain an untouched copy, then map its columns into your ledger. Check date ranges, currency, account or project, and whether the report represents tokens, requests, or costs. If you copy figures manually, enter the report date and a source note so another person can verify them.
  8. Summarize by provider, model, and period. Use spreadsheet filters, pivot tables, or sum formulas to group estimated costs by month and model. Keep provider-reported totals alongside your estimate in separate columns. This makes discrepancies visible instead of blending two different measurement methods.
  9. Reconcile and review. Compare the ledger with provider billing totals for the same account and time window. Check that all projects and models are included, that usage has finished processing, and that taxes, credits, minimums, discounts, or non-token charges are treated consistently. Provider totals may include items your token calculation does not. Mark estimates as provisional until the provider’s report is complete.
  10. Protect the records. Restrict spreadsheet sharing, avoid placing prompts or personal data in notes, and do not publish a workbook containing account identifiers or confidential usage details. If you later automate collection, use a trusted server-side component or a provider-supported secure integration; a spreadsheet alone cannot safely conceal a secret key.

Common Mistakes

Applying one rate to every model: pricing can differ by model, direction of tokens, modality, and feature. Match the exact category and effective date. Mixing units: multiplying a million-token rate by a raw token count inflates the result by a factor of a million. Make the pricing unit explicit in the table. Double-counting cached usage: determine whether cached tokens are included in the input total or reported as a separate subset, then calculate them once using the applicable rate. Comparing mismatched periods: align time zones, start and end dates, projects, and account scope. Treating an estimate as an invoice: provider billing may include adjustments and charges not represented by token counts.

Tips

Use data validation lists for provider and model names to prevent spelling variations from breaking summaries. Keep rates in a dedicated table rather than embedding them in formulas, and include a “rate checked” date. Store raw exports separately from calculated sheets so you can trace every total. Display currency consistently and retain enough decimal precision in calculations; round only for presentation. Add a status column such as “matched,” “needs rate,” or “reconciled” to make missing data easy to find. If reports provide an authoritative cost figure, preserve it and use your calculation mainly for breakdowns and cross-provider comparisons.

Troubleshooting

Totals are far too high or low: inspect the pricing unit and token conversion first, then check whether input and output columns were reversed or counted twice. A model has no rate match: compare its exact identifier with the provider’s pricing categories and check for a dated or retired model; do not substitute a similar name without documenting the assumption. Spreadsheet formulas show errors: confirm imported values are numeric, dates are recognized as dates, and lookup keys match exactly. Remove currency symbols from cells used in arithmetic. Your estimate differs from the dashboard: compare the same time window and project, allow for reporting delays, and list additional charges or credits separately. You cannot export usage: use the provider’s available dashboard totals or enter report figures manually, clearly labeling the reduced detail. Do not solve the problem by embedding an API key in a client-side script.

Frequently Asked Questions

Can I track API costs with no backend at all?
Yes, if manual exports or dashboard entries are acceptable. You can calculate and summarize them in a spreadsheet without a server.

Can a browser-only tool safely call providers using my secret keys?
No. Keys included in browser code or requests can be exposed. Keep them out of client-side tools; use provider dashboards or a secure server-side integration.

Will my spreadsheet total exactly match my bill?
Not necessarily. It may omit credits, taxes, tool or modality charges, reporting adjustments, or other billing rules. Treat it as an estimate and reconcile it with the provider’s records.

How often should I update pricing?
Check pricing whenever you add or change a model and periodically thereafter. Save dated rates so past periods remain reproducible.

Should I store prompts in the ledger?
Usually not. Token counts and model identifiers are enough for cost tracking; avoid recording prompt content unless there is a clear, approved need.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →