How do I see what my positions looked like on a past date?

Recipe verified on 2026-10-29 Ran on a private test instance

Short answer

Keep each day's positions as a dated version of one table, then query it AS OF that date: SELECT ... FROM risk.book AS OF LASTDATA BETWEEN #2026-10-01# AND #2026-10-01# returns the book as it stood that day, whatever has changed since.

Steps

Connect your assistant to your instance (this writes, so it needs your own instance with a ReadWrite key from the accounts portal; the public demo is read-only). In Claude Code:

claude mcp add --transport http mcpdata https://[you].mcpdata.org/mcp --header "Authorization: Bearer $CDB_API_KEY"

Replace [you] with your instance's name. Other apps: Connect your AI.

Load the daily files as a history.

cdb-localdata add ./samples/positions --to https://[you].mcpdata.org --namespace pastdate --name pastdate
cdb-localdata sync

Ask for a past date and the latest.

import cdb
s = cdb.Session("https://[you].mcpdata.org")
sql = "SELECT desk, COUNT(*) AS positions FROM pastdate.book AS OF {} GROUP BY desk ORDER BY desk"
then = s.execute_sql(sql.format("LASTDATA BETWEEN #2026-10-01# AND #2026-10-01#")).to_dataframe()
now = s.execute_sql(sql.format("LASTDATA")).to_dataframe()
print(then.merge(now, on="desk", suffixes=("_on_2026_10_01", "_latest")).to_string(index=False))

Ask your question. Give your AI this prompt:

Using mcpdata, save a view of how many positions each desk held in pastdate.book on 2026-10-01, and report it.

What your AI does: the tool calls it makes for this prompt, and what came back

create_view(name="pastdate.by_desk_2026_10_01", select_sql="SELECT desk, COUNT(*) AS positions FROM pastdate.book AS OF LASTDATA BETWEEN #2026-10-01# AND #2026-10-01# GROUP BY desk") → created true, view pastdate.by_desk_2026_10_01

create_report(name="Positions by desk on 2026-10-01", view="pastdate.by_desk_2026_10_01") → report_id 5945b26064bb4c45b752c58fa689b498

run_report(report_id="5945b26064bb4c45b752c58fa689b498") → status ok, rows 4

Output of the steps above, from the last run

desk  positions_on_2026_10_01  positions_latest
  Credit                       50                49
Equities                       63                61
      FX                       45                43
   Rates                       42                41

The assistant needs mcpdata connected (step 1) to run the query.

Overwriting the positions table each day loses history; keeping files around loses the ability to query them together. Versions give you both: one table, any date. AS OF LASTDATA is the latest day, AS OF ALLVERSIONS every day at once.

How do I see what my positions looked like on a past date?

Steps

Why this works

Related questions