# Spreadsheets and Data Handling for Traders Build an auditable analysis workbook from trade records. Use this workbook alongside the course. Write your answers before opening the solutions. Practical work is self-reviewed; scored knowledge checks are in the Academy. ## 1. Import and structure ### CSV types and delimiters CSV is a text format whose delimiters, decimal conventions and quoting must be interpreted correctly. Inspect the raw header and several rows before analysis. A value imported as text may look numeric while behaving differently in a formula. ### Dates and timezones Dates need an explicit timezone and format. The string 03/04 is ambiguous without a convention. Preserve raw timestamps and create a separate normalized field rather than overwriting the only copy of the source representation. ### Unique identifiers Use stable identifiers for accounts, orders and executions. A timestamp or price alone may not be unique. Record the source file and import batch so an apparent duplicate can be traced without discarding genuinely separate fills. ### Missing and duplicate rows Missing values are different from zeros. A missing commission is not evidence of free execution. Flag incomplete fields and define whether a calculation can proceed; avoid silently replacing every blank with zero. ### Worked example A CSV contains quantity values as text and blank fee fields. A sum appears plausible but omits some rows. The import should be corrected and reconciled before its total is used to evaluate a strategy. ### Independent exercise Design an import checklist that validates row count, data types, timestamp interpretation, identifiers and missing fees. My inputs and assumptions: My calculation or decision: Evidence that would change my conclusion: ## 2. Calculations ### Absolute and relative references Relative references change as formulas are copied; absolute references keep a specified cell fixed. A risk-budget formula that accidentally shifts to another row can produce plausible but incorrect quantities. Inspect copied formulas at the beginning and end of a range. ### Unit-aware formulas Write units beside inputs and intermediate results. A multiplier table should distinguish price units, contract quantity and account currency. Consistent units make it easier to spot an inverse conversion or missing contract multiplier. ### Lookup tables Lookup tables centralize instrument specifications and fee assumptions. Use exact keys and explicit missing-key handling. An approximate match can silently assign a neighboring instrument's multiplier, which is unacceptable for exposure calculations. ### Error handling Error handling should reveal invalid inputs rather than hide them. Replacing every spreadsheet error with zero can turn missing data into an apparently safe trade. Show the reason and suppress dependent conclusions until the input is corrected. ### Worked example A copied formula references B2 for the budget, then B3, which contains a different value. If the budget was intended to be fixed, an absolute reference or named input is required; both outputs could otherwise look reasonable. ### Independent exercise Explain how to test a copied sizing formula and an instrument lookup using one normal row, one unknown symbol and one missing stop distance. My inputs and assumptions: My calculation or decision: Evidence that would change my conclusion: ## 3. Analysis ### Pivot tables Pivot tables summarize records by selected fields, but their results depend on source range, aggregation and refresh state. Check that new rows are included and that a displayed sum is not accidentally a count or an average. ### Grouped returns Group returns using consistent definitions. Averaging trade percentages is not necessarily an account return, especially with changing size or overlapping exposure. State what the statistic measures and preserve account-equity calculations separately. ### Equity curves An equity curve accumulates results under a cash-flow convention. Deposits are not trading profits. Distinguish balance from marked-to-market equity when evaluating drawdown or open risk; a closed-trade curve can hide large interim losses. ### Avoid misleading chart scales Choose chart scales and labels that expose the data honestly. A truncated axis can exaggerate small differences, while excessive smoothing can hide drawdowns. Include units, dates and the cost basis of the plotted result. ### Worked example An account begins with 1,000, earns 50 from trades and receives a deposit of 500. Ending balance is 1,550, but trading profit is 50. Treating the full 550 increase as strategy profit misclassifies the cash flow. ### Independent exercise Construct separate columns for trade P&L, deposits and withdrawals, then calculate ending balance for the example. My inputs and assumptions: My calculation or decision: Evidence that would change my conclusion: ## 4. Quality controls ### Reconciliation totals Reconciliation totals compare imported records with an authoritative statement or a deliberately specified exercise dataset. Check counts, quantities, fees and P&L. Matching only one total can conceal offsetting errors. ### Locked source sheets Keep a read-only source sheet or immutable source file and perform transformations separately. This preserves the ability to investigate an unexpected result. Manual edits should be logged with reasons and retained alongside the original values. ### Versioned assumptions Version assumptions such as fees, currency conversion and trade-grouping rules. Two reports built from the same fills can differ because their assumptions changed. A report should identify both the data version and calculation version. ### Reproducible export A reproducible export includes the output, source references and enough instructions to rebuild it. Hide personal identifiers in shared learning examples, but do not remove the structural information needed to understand the calculation. ### Worked example Two erroneous rows add +10 and −10, leaving aggregate P&L unchanged. A total-only check passes, but execution identifiers and quantities reveal the mistakes. Multiple reconciliation dimensions are therefore useful. ### Independent exercise Specify four reconciliation checks and a change log entry for correcting an imported fee without erasing its original value. My inputs and assumptions: My calculation or decision: Evidence that would change my conclusion: ## Course project Import a sample CSV, reconcile totals and publish a documented performance worksheet. ### Self-review rubric - Concepts and reasoning: 25% - Calculations, data and evidence: 30% - Process and risk controls: 25% - Limitations and communication: 20% Record one correction and one next practice task. This rubric is not automatically graded. ## Worked solutions ### Exercise 1 Compare source and imported row counts, explicitly parse numeric and date fields, retain identifiers, and report missing fees. Reconcile a few rows manually and the full totals before using the dataset. ### Exercise 2 The normal row should match an independent calculation. The unknown symbol should produce a specific missing-specification error; the missing distance should block sizing. Neither invalid case should become a zero-risk result. ### Exercise 3 Ending balance equals 1,000 + 50 + 500 = 1,550. The performance report should show 50 trading P&L and 500 external contribution, with a stated method if calculating a return percentage. ### Exercise 4 Check row identifiers, quantities, fees and P&L. Log the original fee, corrected fee, source evidence, date and reason. Rebuild dependent outputs and confirm that unrelated records remain unchanged. ## Further reading - https://support.microsoft.com/en-us/excel - https://www.itl.nist.gov/div898/handbook/