Start with three records, not one ratio
A volume number counts activity during an interval. Open interest records outstanding contracts at a stated observation point. Comparing one volume figure with one OI figure can be descriptive, but it cannot tell you how OI changed. For that job you need an earlier snapshot, a later snapshot and a volume interval that really connects them.
This worksheet keeps those three roles explicit. It asks for the product, expiry, unit, timestamps and evidence reference before performing arithmetic. A number that looks reasonable may still describe another month, another session or a different unit. Fixing the label after calculating can hide the mismatch that matters most.
Read the SGX report one contract month and field at a time
Before comparing fields, retain the contract-month label and the stated scope of each column. A total across expiries cannot silently become one expiry’s activity in this worksheet. Treat a scope you cannot establish as unresolved; a similar-looking product name does not supply that missing evidence.
The archived SGX iron-ore report dated 13 January 2023 separates contract months, product groups, T volume, T+1 volume and open interest in contracts. It is used here as a historical schema example. Its dated prices and counts are not today’s market, and the labels alone do not establish your required start and end instants.
An interval match needs more than matching calendar dates
The two OI snapshot instants define the interval being examined. Enter the exact start and end assigned to your volume record. The worksheet requires those instants to match the OI boundaries, including their UTC offsets. It does not decide that two records belong together because both say Tuesday or because their filenames have the same date.
Singapore time is shown to make the supplied records easier to inspect. That display is not an exchange session calendar. A T+1 label is not converted into a universal midnight boundary, and the tool does not add T and T+1 columns automatically. Use documented period definitions for the particular report; leave the comparison unresolved if those definitions are missing.
Keep the count unit intact
A contract count is not a coin amount, a dollar notional or a quantity of physical commodity. This worksheet deliberately performs no multiplier conversion. Entering tonnes with a contracts label would misstate the evidence even if the resulting count happened to be a whole number. Preserve the source unit and use a separate, dated specification before any conversion elsewhere.
An explicit zero is a known entered count. A blank is unknown, and a dash copied from a report is not accepted as zero. The tool accepts only non-negative whole counts, preserves large integers exactly and refuses fractional contracts. A scope or evidence mismatch withholds the derived OI change and the model together; it never silently uses a smaller apparently valid subset.
The first calculation is only a difference
For the fictional data, the closing count of 120 minus the opening count of 100 gives a change of +20 contracts. That is a net difference between two entered snapshots. It does not say there were exactly 20 new long positions, that 20 traders arrived, or that prices should rise.
CME’s introductory explanation treats open interest as outstanding futures contracts, with changes when positions open or close. Every matched futures contract has opposing sides. A positive net OI change is therefore not a count of bullish people. This tool reports neither trader identities nor directional sentiment.
| Record | Count | Meaning |
|---|---|---|
| First OI snapshot | 100 contracts | Outstanding count at the first supplied instant |
| Second OI snapshot | 120 contracts | Outstanding count at the later supplied instant |
| Interval volume | 70 contracts | Activity over the exact supplied interval |
| OI change | +20 contracts | Later stock minus earlier stock |
Sources: CME Group — Open Interest.
Use the optional model only when its assumptions are appropriate
The simplified model divides matched traded contracts into three groups: both sides open positions, both sides close positions, or one side opens while the other closes. Call their counts O, C and T. Under a clean interval with no other adjustments, O minus C equals the OI change, and O plus C plus T equals volume.
The sequence must also be possible. Start with the earlier outstanding count. Each opening adds one contract; each closing removes one; a transfer leaves the count unchanged. Closing and transferring both need an existing contract, and the count must never fall below zero. The equations alone would wrongly permit transfers in an empty market.
This is conditional arithmetic, not a reconstruction of exchange records. Expiry, exercise, administrative correction, different reporting cuts or a changed contract scope can invalidate those equations for your dataset. The default example states the assumptions only to demonstrate them. For an unresolved real dataset, choose the unconfirmed setting and retain just the scoped difference.
The ranges are linked to one another
With a change of +20 and volume of 70, one possible explanation is O=20, C=0, T=50. Another is O=45, C=25, T=0. Between them, increasing C by one requires O to rise by one and T to fall by two. The actual mixture is not identified by the totals.
The displayed opening range is 20–45, the closing range is 0–25 and the transfer range is 0–50 in steps of two. Do not combine O=45 with C=0 simply because each appears within its own range; together they fail the net-change equation. The table is a set of coupled possibilities, not three independently selectable forecasts.
To see why the stock condition matters, suppose both snapshots are zero. Volume of one then has no solution in this model. With volume of two, the only possible counts are one opening, one closing and no transfers. With volume of three, the counts are one opening, one transfer and one closing. These are hypothetical feasible sequences, not claims about reported trades.
A contradiction rejects the model, not the venue
If you keep OI change at +20 but enter volume of 10, the simplified model has no solution: the absolute net change exceeds the supplied traded count. The tool shows that inconsistency without replacing the data, clipping the change or declaring that the market report is wrong.
Investigate missing session coverage, different expiry scope, corrections and non-trade adjustments in the source. A blank volume produces an unknown result instead of that contradiction. A zero volume with unchanged OI permits the all-zero model; zero volume with a non-zero OI change does not. These are materially different cases.
Keep the evidence with the result
The CSV preserves every entered count, original timestamp, reference and row issue, plus the optional model state. Formula-like identifiers are protected as text for spreadsheet export. Negative OI changes are also exported safely; their sign remains visible. No source file is modified.
Everything runs in your browser without an account connection or data upload. Editing any input invalidates the previous export until you calculate again. Without JavaScript, the worked example and equations remain readable. After completing the review, the optional link leads to this site’s separate English-language offer; the worksheet does not depend on following it.
Worked examples
Example 1
The fictional opening OI is 100 contracts, closing OI is 120 and matched interval volume is 70. OI changes by +20 contracts. Under the stated simplified model, both-opening contracts can range from 20 to 45, both-closing from 0 to 25, and transfers from 0 to 50 in steps of 2. These ranges are coupled, not separate choices. Actual classifications and price direction remain unknown.
Inspect example inputs
{
"product": "DEMO-SGX-ONE-EXPIRY",
"expiry": "2026-11",
"as_of": "2026-10-20T10:00:00Z",
"model": "trade_only_no_adjustments",
"rows": "role|product|expiry|unit|value|start_at|end_at|reference\noi_start|DEMO-SGX-ONE-EXPIRY|2026-11|contracts|100||2026-10-19T09:00:00Z|DEMO opening snapshot\noi_end|DEMO-SGX-ONE-EXPIRY|2026-11|contracts|120||2026-10-20T09:00:00Z|DEMO closing snapshot\nvolume|DEMO-SGX-ONE-EXPIRY|2026-11|contracts|70|2026-10-19T09:00:00Z|2026-10-20T09:00:00Z|DEMO complete interval volume"
}Questions about the method
Can I add volume to open interest?
No. One is interval activity and the other is an outstanding snapshot. The model relates the change between two snapshots to matched interval volume.
Can the opening and closing bounds identify actual trades?
No. Multiple coupled classifications fit the totals, and the assumptions may not hold for your reports.
Does a matching expiry prove the reports are comparable?
No. Units, product, evidence and exact interval boundaries also matter. Entered metadata is not independent verification.
Why can zero OI at both ends still require an opening?
If there is any activity, a contract must first exist before it can close or transfer. The model can allow an opening followed by a closing between two zero snapshots; it cannot transfer a contract that never existed.
Primary sources and scope
- SGX — 13 January 2023 Iron Ore DSP reportChecked 2026-10-21. Historical report schema: product groups, contract months, separate T and T+1 volume columns and open interest in contracts. Not a current product specification, session clock rule or current observation.
- CME Group — Open InterestChecked 2026-10-21. General distinction between outstanding contract count and changes from opening/closing trades. Does not verify the worksheet’s clean-interval assumptions or SGX data.
Examples explain a method. They do not predict prices or establish where a trading service is available. Check the named source before relying on future schedules.