# Connect my broker's API to my Portle ledger **How to use this file:** paste the whole thing into a coding assistant that can run commands on your computer (Claude Code, Cursor, Codex, or similar). Fill in the two blanks in "What I have" first. Everything else is instructions for the assistant. --- ## What I have - **Broker:** `` - **Ledger sheet:** `` --- ## Your task Write a script I can run on a schedule that reads my executed trades from my broker's API and **appends the missing ones** to the `Transactions` tab of my Google Sheet. You are a guest in that sheet. It is my financial record, not your output. ## Rules you may not break 1. **Append only.** Never delete a row. Never modify a row you did not add in this run. Never clear, rewrite, or reorder the tab. If you think a row is wrong, report it — do not fix it. 2. **The first run is a dry run.** Print what would be added and change nothing. I will look at it before you are allowed to write. 3. **Running twice must do nothing the second time.** Prove this by running the dry run again after a real run. If it wants to add anything, the script is wrong. 4. **Find columns by name, not by position.** Read the header row and place each value under its own header. I may have reordered columns or added my own. If a value you produced has no matching column, stop with an error instead of dropping it silently. 5. **Never invent a number.** If the API does not give you a fee, leave the cell empty. Do not estimate, round to something convenient, or carry a value over from another row. 6. **Keep my credentials out of the code.** Environment variables or a local secrets file. Never a literal in a source file, never anything you commit. ## The sheet contract One tab, `Transactions`. The header row is exactly these names, and the app finds every column **by name**, so their order is not fixed: ``` Date Account Type Symbol Quantity Price Amount Fee Tax Currency AcquiredOn Note Id FxToBase Void ``` `Type` is one of: | Type | Meaning | |---|---| | `OPENING` | A position appears without cash moving: starting the ledger, a spinoff, a ticker change | | `BUY` | A purchase. Fee and tax are added to the cost basis | | `SELL` | A sale. Fee and tax come out of the proceeds. Lots are consumed first in, first out | | `DIVIDEND` | `Amount` is before tax; `Tax` is withheld and the rest reaches cash | | `DEPOSIT` | Money put in. Excluded from investment performance | | `WITHDRAW` | Money taken out | | `FEE` | A cost while holding, such as an ADR fee. Kept out of the cost basis | | `SPLIT` | `Quantity` is the ratio: `4` for a 4-for-1, `0.1` for a 1-for-10 reverse. Total cost does not change | | `ADJUST` | Correct quantity or cost by a delta: spinoff cost allocation, fixing a mismatch | **Columns you should usually leave empty — the app fills them better than you can:** - `Amount` — give `Price` and `Quantity` and the app computes the total. Use `Amount` alone only when the broker gives you a total and no per-share price. - `FxToBase` — one unit of this row's currency in my base currency. **Leave it blank and the app fetches that day's rate and says on screen that it did.** Anything written here is never overwritten, so a wrong guess is permanent. - `AcquiredOn` — only if the broker tells you the original acquisition date. - `Void` — mine to write. Any text here means "leave this row out of every calculation." The row stays on the sheet. Never write this column. `Account` must match a name on the `Accounts` tab. Read that tab and use an existing name. If there is no account for this broker, stop and tell me to add one rather than inventing a name. ## Idempotency: the `Id` column This is the whole design. Every row you add gets a **stable, deterministic id** in the `Id` column, built from something the broker will report the same way forever — its order id: ``` : ``` for example `toss:LeBbP5dc…` or `kis:0000123456`. Before adding anything: read the `Transactions` tab, collect every value in the `Id` column into a set, and skip any trade whose id is already there. Two rules that follow from this and are easy to get wrong: - **A row I marked `Void` still counts as already present.** `Void` is my judgment that a row should not be counted, not a statement that the trade never happened. Re-adding it overrides me. - **If the `Id` column is missing from the header row, stop.** Without it you cannot see what is already recorded and you will duplicate my entire ledger. Do not fall back to matching on date and amount — partial fills make several trades look identical. ## What you must find out from my broker's documentation Read the actual API docs before writing code. Report each of these back to me, because several of them decide whether this can work at all: 1. **Authentication.** What credentials do I create, and how do they become a request token? How long does a token last? 2. **Is there an IP allowlist?** Many brokers only accept calls from IP addresses I register in advance. If so, this script can only run somewhere with a fixed IP — my own computer or a server, never a serverless host and never a phone. 3. **How many tokens may exist at once?** Some brokers keep exactly one valid token per client and silently invalidate the previous one on reissue. If so, nothing else may call the same API at the same time, and this script must not run concurrently with any other job of mine. 4. **How far back does trade history go?** If there is a window — 3 months, 12 months — say so plainly. It means this script can maintain a ledger going forward but cannot rebuild the past, and anything older must already be in the sheet. 5. **Rate limits**, and whether they are per endpoint group. 6. **Does the API report cash movements and dividends at all?** Read the next section before you answer. ## The part that usually goes wrong: cash Most broker APIs give you executed orders and current holdings, and **nothing about deposits, withdrawals, currency conversion, or dividends.** If that is true for my broker, then appending only `BUY` and `SELL` rows makes my ledger show cash that never arrived, and every cash figure in the app becomes wrong. Do not paper over this. Pick one and tell me which: - **Preferred: append the trades and report the gap.** After each run, print what the ledger's cash balance is versus what the broker reports, and let me enter the deposits myself. Honest and boring. - **Only if I ask for it: reconstruct the cash rows.** Generate the `DEPOSIT`, `WITHDRAW`, and currency-conversion rows the trades imply, give each one a stable id of its own (`:funding:` and so on), and put the word `Reconstructed` at the start of every `Note` so I can always see which rows are inferred rather than reported. Never let an inferred row look like a reported one. The same applies to dividends: if the API does not report them, say so rather than deriving them quietly. ## Running it on a schedule Once I have confirmed a dry run and one real run: - Schedule it on a machine that stays on and has the IP I registered. - Run it after market close, not during the session. - Write output to a log file, and make failure loud enough that I notice. - If I already have another job that calls this broker, run this **inside that same job, sequentially** — see the token question above. ## Before you tell me you are done - [ ] Dry run prints a readable list of what it would add - [ ] A real run added exactly what the dry run promised - [ ] A second dry run reports nothing to do - [ ] I hand-edited a row and re-ran, and my edit survived - [ ] I wrote something in `Void` and re-ran, and that row was not re-added - [ ] The credentials are not in any file you would commit - [ ] You told me the answers to all six documentation questions above - [ ] You told me which cash approach you took, and why --- *This file is maintained at https://portle.quest/prompts/broker-api-to-portle-sheet.md — Portle never sees your broker credentials or your sheet. Everything here runs on your own machine, against your own copy.*