# Crypto XLSX initial snapshot dry-run pattern

Use this reference when the user explicitly approves a controlled dry run against a real crypto holdings spreadsheet from Google Drive or another private source.

## Safety boundary

- Treat real crypto XLSX/CSV files as private financial data.
- Download/read the real file only into `mktemp`/external runtime; never copy it into the repo, tests, fixtures, examples, docs, or reports.
- Print only aggregate counts and labels/mapping statuses; never print quantities, CHF values, prices, row-level values, DB paths, or raw CSV content.
- Ignore legacy/current-value columns unless the user explicitly asks otherwise. The crypto dashboard should calculate current valuation from local price data/CoinGecko later.
- For validation, it is acceptable to commit prerequisite wallet rows into a temporary dry-run DB so holding rows can resolve FK/wallet references, but holdings must remain `commit=False` unless the user explicitly approves productive import.
- Remove the temp runtime directory and verify `git status --short` before reporting.

## Known XLSX shape: `260514_ Zusammenstellung aller Coins.xlsx`

The file is a hand-maintained holdings matrix, not a canonical CSV:

- Sheet: `2024`
- Row 1: context/snapshot/FX labels
- Row 2: wallet/platform headers
- Column A: coin/asset labels
- Columns B-I: wallet/platform quantities
- Column J: calculated total quantity
- Columns K-Q: legacy CHF/value/price formulas; ignore for current valuation/import unless explicitly requested
- Empty and total rows must be skipped.

Wallet normalization decisions from the first controlled dry run:

- `Swissborg` -> `SwissBorg` (`Exchange`)
- `Coinbase` -> `Coinbase` (`Exchange`)
- `Binance` -> `Binance` (`Exchange`)
- `KuCoin` -> `KuCoin` (`Exchange`)
- `Metamask` -> `MetaMask` (`DeFi`)
- `FireFly IOTA` -> `Firefly IOTA Wallet` (`Software Wallet`)
- `Yoloi Wallet` -> `Yoroi Wallet` (`Software Wallet`)
- `Nami Wallet` -> `Nami Wallet` (`Software Wallet`)

User-confirmed asset mappings:

- `Tether USD` -> symbol `USDT`, CoinGecko ID `tether`
- `Swissborg` -> symbol `BORG`, CoinGecko ID `swissborg`
- `Sundae` means SundaeSwap, but leave out unless CoinGecko ID is explicitly confirmed.
- `S Sonic` and `NIGHT`: ignore in first dry run; count as skipped/ignored, not blocked.
- `Polygon MATIC`: mark as MATIC/POL migration review; do not auto-import until the user chooses old MATIC vs current POL handling.

## Conservative mapping policy

For the first real-file dry run, include only assets that are either clearly safe or explicitly confirmed by the user. Leave all ambiguous symbols out of the importable set and count them as `skipped/needs_manual_review`, not failed/blocking.

Common labels requiring manual review rather than auto-import:

- `ADA Cardano`, `Avalanche AVAX`, `Bitcoin BTC`, `DOGE`, `Ethereum ETH`, `SOL Solana`: symbol collisions/wrapped variants exist even if the likely mapping is obvious.
- `Amp`, `Chainlink`: source label lacks explicit symbol.
- `IOTA IOT`: source label/symbol inconsistent.
- `LUNA Terra`, `LUNC Terra Classic`, `USTC Terra Classic`: Terra legacy/migration risk.
- `NuCypher NU`, `NETVR`, `MATIC/Polygon`: migration/rebrand risk.
- `Sundae`, `SKY`, `SAROS`, `NIGHT`, `S Sonic`: require manual confirmation or user-directed ignore.

## Decimal handling

- Read quantity cells from wallet columns only.
- Convert Excel numeric values immediately to Python `Decimal` and write `format(d, "f")` strings to runtime CSV.
- Detect and count scientific notation and formula quantity cells, but do not print their values.
- Reject non-positive or unparsable quantities for importable rows.
- Count formula/scientific-notation issues separately for the whole file and for the safe importable subset when useful.

## Dry-run implementation sequence

1. Locate/download the source file into `mktemp` only. If a Google Drive CLI downloads to a path without `.xlsx`, copy it inside the same temp dir to `source.xlsx` before passing it to `openpyxl`.
2. Parse structure with `openpyxl` using `data_only=False` for formulas and `data_only=True` for calculated values.
3. Extract source wallet headers from row 2, columns B-I, apply normalization.
4. Extract asset labels from column A starting row 3; skip blank and total/sum rows.
5. Build runtime-only `crypto_wallets.csv` and `crypto_holdings_initial.csv` under temp/runtime.
6. Create a temp SQLite DB, apply migrations, commit wallets into the temp DB for FK validation, then run holdings importer with `commit=False`.
7. Verify temp DB has zero committed `crypto_holdings` rows.
8. Cleanup temp dir and unset credentials/env vars.
9. Run `git status --short` and report only aggregate counts, mappings, ignored labels, manual-review labels, and importability verdict.

## Reporting template

Report only:

- rows read
- asset rows detected
- wallets detected / normalized
- coins detected
- safely mapped coins
- importable holdings count
- ignored coins count and labels
- uncertain/manual-review coins count and labels/reasons
- missing CoinGecko ID count for importable set
- decimal problem count
- formula quantity cell count
- scientific-notation quantity count
- blocked row count
- snapshot date status: readable vs uncertain; if sensitive, say readable but do not print the date unless needed
- dry-run technical importability verdict
- repo/temp cleanup status

Do not include real quantities, prices, CHF values, generated CSV contents, DB dumps, or reports with financial numbers.