First reconcile the unit you actually hold
A Canadian-dollar display can make an account easier to read, but it cannot explain a missing amount in the original cash ledger. Start with one account scope, one currency or asset unit and a defined period. Reconcile the units first. Only then attach a separate CAD reference valuation to the expected balance, recorded balance and unexplained difference.
This extends the existing cash reconciliation worksheet. The original gross-versus-net calculation and its worked example remain below. The added Canadian layer records the direction and date of a reference rate and leaves CAD results unknown when that evidence is missing. It does not connect to an account, upload a statement or retrieve an exchange rate.
Use one explicit cash equation
Expected closing cash equals opening cash plus signed external flows plus the net trading component. The reconciliation difference is recorded closing minus expected closing. A positive difference means the recorded balance is larger than the inputs explain; a negative difference means it is smaller. Neither sign identifies the cause.
Opening and closing must describe the same wallet, included subaccounts, currency and boundary. Do not substitute total account equity for cash: open positions and inventory valuations are outside this calculation. A transfer between two accounts that are both inside your scope is not a new external deposit. The tool cannot detect the true scope of a pasted number, so write it down before calculating.
Signed flows make the direction inspectable
For the simple form, deposits and withdrawals are nonnegative magnitudes: add deposits and subtract withdrawals. For a detailed record, use signed flow rows instead. A deposit is positive, a withdrawal is negative, and an other adjustment needs a short explanation. The two input modes are alternatives; entering both would double-count movements.
Give each detailed movement a unique local identifier. Reject duplicate identifiers before adding the rows. This catches one common copy-and-paste error without pretending to verify the statement. If a refund, funding payment or manual adjustment is already included in your realized result, do not list it again as a separate flow. Missing source records remain missing; a balanced equation is not an independent audit.
Define fees once
In gross mode, subtract the supplied nonnegative costs once from realized P&L. In net mode, the realized value already contains those costs, so the costs field is retained for explanation but not subtracted again. A nonzero costs entry in net mode produces a warning rather than an artificial shortfall. If the separate fee amount is unavailable in net mode, leave it blank: its amount remains unknown while the net result is used unchanged.
Detailed flow rows must not contain a second fee category: costs belong in the dedicated field when they are not already included in P&L. This does not automatically solve every provider convention. Read the export definition and decide which source amount is gross, net or a ledger movement. The labels you enter describe your record; the calculator cannot certify them.
The original cash example still reconciles the same way
The account numbers in this example are hypothetical. Start with 1,000 USD, add 200 in deposits, subtract 50 in withdrawals, add 30 of gross realized P&L and subtract 5 of costs. Expected closing cash is 1,175 USD. Recorded cash of 1,180 USD leaves+5 USD to investigate.
If the provider instead supplies 25 USD of already-net realized P&L, choose net mode. The expectation remains 1,175 and the difference remains+5. Subtracting 5 again would manufacture a different answer. Keep the original-unit result visible even when you add a CAD column.
| Component | Signed contribution, USD |
|---|---|
| Opening cash | 1000 |
| Deposit | +200 |
| Withdrawal | -50 |
| Gross realized P&L | +30 |
| Costs in gross mode | -5 |
| Expected closing | 1175 |
| Recorded closing | 1180 |
| Recorded minus expected | +5 |
What the Canadian reference rate actually says
The Bank of Canada daily page expresses rates as Canadian dollars for one unit of foreign currency. It describes daily averages published by 16:30 Eastern on business days, with possible delays. This is an indicative reference, not a rate that the worksheet executed for you.
The Bank's terms distinguish statistical and analytical use from an execution benchmark. Keep an actual conversion receipt separate if money was exchanged. A bank or venue could apply its own rate and fees. Multiplying a cash balance by the reference rate adds a valuation view; it does not add Canadian cash to the account or prove an FX trading gain.
Sources: Bank of Canada - Daily exchange rates, Bank of Canada - Exchange-rate terms and limitations.
A dated CAD example, separate from the cash equation
On October 20, the Bank of Canada table showed 1.4097 CAD per USD for October 19, 2026. Applying that historical reference to the hypothetical cash result gives 1,656.3975 CAD for the expected 1,175 USD and 1,663.446 CAD for the recorded 1,180 USD. The+5 USD gap has a reference value of+7.0485 CAD at that same rate.
The multiplication is exact decimal arithmetic for the entered values. It does not assert that the account converted at 1.4097 or held either CAD amount. Both balances use one chosen reference date here; this is not a multi-date FX-performance calculation. The original+5 USD remains the actual reconciliation question.
| Hypothetical USD quantity | Entered dated reference | CAD reference value |
|---|---|---|
| Expected 1175 | 1.4097 CAD per USD, 2026-10-19 | 1656.3975 |
| Recorded 1180 | Same reference | 1663.446 |
| Unexplained difference+5 | Same reference | +7.0485 |
| Any quantity with no valid reference | Unknown | Unknown, never 0 |
Sources: Bank of Canada - Daily exchange rates.
Missing, delayed and differently dated rates
The Bank's backgrounder explains that publication can be delayed and a rate may be marked not available for that day. Its listed daily currencies are a specific set of fiat currencies. A missing entry is not a zero exchange rate, and a USD reference must not be silently applied to a token called USDT or USDC.
This worksheet requires the entered rate date to equal the requested reference date. It will not fill a weekend with Friday's number automatically. You may deliberately choose another requested date for a separate historical reference view, but retain that date in the export and do not label it the original date. If the rate, date, direction, currency or source information is incomplete, cash reconciliation still works while the CAD layer stays unknown.
Sources: Bank of Canada - Background information on foreign exchange rates.
Precision and evidence travel together
Enter plain signed decimals without currency symbols, grouping commas or scientific notation. The worksheet supports up to 8 decimal places for cash amounts and reference rates. Calculations use integer decimal coefficients; displayed exact strings do not pass through binary floating-point arithmetic. Multiplication can produce up to 16 decimal places, which are preserved rather than rounded silently to cents.
Save the exact account scope, period, input mode, fee convention, flow identifiers and chosen reference-date evidence with the result. A zero cash difference means only that the supplied arithmetic balances. A missing CAD value is useful information: it prevents an unavailable rate from turning into an invented portfolio value. The export is a local snapshot, not a subscription to updated rates.
What this worksheet does not calculate
The reference layer does not value cryptocurrency inventory, calculate capital gains, prepare a CRA return or decide which exchange-rate method is appropriate for a tax filing. It does not establish Canadian residency, service eligibility, a local office or a regulatory licence. Those questions require their own facts and processes.
The existing resource comparison remains at /learn/crypto-record-reconciliation-services/, and the learning hub remains at /learn/. This resource is useful in full without opening the optional Telegram offer. Use the output to identify a concrete missing record or valuation assumption, rather than calling every increase in displayed value trading profit.
Worked examples
Example 1
- account scope
- DEMO single USD cash wallet
- currency
- USD
- period start
- 2026-10-01
- period end
- 2026-10-19
- opening balance
- 1000
- deposits
- 200
- withdrawals
- 50
- realized pnl
- 30
- pnl mode
- gross
- costs
- 5
- reported closing
- 1180
- fx
- requested_date: 2026-10-19; rate_date: 2026-10-19; checked_at: 2026-10-20; base_currency: USD; quote_currency: CAD; direction: CAD_per_unit; rate: 1.4097; source_url: https://www.bankofcanada.ca/rates/exchange/daily-exchange-rates/
- status
- ok
- expected closing
- 1175
- reported closing
- 1180
- difference
- 5
- net trading
- 25
- costs subtracted
- 5
- fx status
- entered_reference
- cad expected
- 1656.3975
- cad reported
- 1663.446
- cad difference
- 7.0485
Hypothetical USD cash inputs, with the separately dated official reference rate. No actual FX execution is claimed.
Example 2
- UTC instant
- unknown
- status
- ok
- expected closing
- 1175
- difference
- 5
- net trading
- 25
- costs subtracted
- 0
Costs already included in net P&L are not deducted again.
Example 3
- UTC instant
- unknown
- status
- ok
- expected closing
- 1175
- difference
- 5
- fx status
- unknown_missing_reference
- cad expected
- unknown
- cad reported
- unknown
- cad difference
- unknown
An unknown CAD reference does not destroy the valid USD calculation and is never replaced with zero.
Example 4
- UTC instant
- unknown
- status
- ok
- fx status
- unknown_date_mismatch
- cad expected
- unknown
No automatic carry-forward from October19 to an October20 request.
Questions before you use the result
Does a zero reconciliation gap prove every statement is correct?
No. It means the supplied equation balances. Missing or offsetting errors can still exist; source records and scope need review.
Why is CAD unknown while my cash result is available?
Cash arithmetic uses your original unit. CAD needs separate dated rate evidence. Missing, zero, wrongly directed or differently dated rates must not become a zero valuation.
Can I use the USD rate for a USD-pegged token?
This worksheet does not assume parity. The asset unit must match the reference currency; a token balance is not automatically a USD cash balance.
Primary sources and scope
- Bank of Canada - Daily exchange ratesChecked 2026-10-20. Rates are quoted as CAD per one foreign-currency unit. The displayed October 19, 2026 US-dollar entry was 1.4097. Daily averages are scheduled by 16:30 ET each business day, subject to possible delays.
- Bank of Canada - Background information on foreign exchange ratesChecked 2026-10-20. The current daily list covers specific fiat currencies. Unavailable or delayed rates are distinct from a numeric zero. The daily reference uses its own publication and calculation convention; it is not an actual account conversion.
- Bank of Canada - Exchange-rate terms and limitationsChecked 2026-10-20. Exchange rates are indicative and may differ from executable or actually executed rates. The rates serve statistical and analytical purposes rather than an execution benchmark.
Prepared from the sources listed above. Examples and calculations explain a method; they do not predict prices or certify an investment. Geographic time references do not establish where a trading service is available. Dated schedules can change: consult the named publisher before relying on a future event.