What disappeared before your spreadsheet opened?
A price column containing 101.20, an empty cell and 0 contains three different observations about the file. The first is a decimal value. The second contains no characters. The third explicitly contains a zero. Replacing the empty cell with zero creates information that the file did not supply.
This worksheet is for reviewing exported or manually assembled price records before making a chart or summary. Its result is a classification, not a cleaned trading dataset. It keeps the original text available so you can investigate a flagged row without guessing what used to be there. The sample is invented; it is not an exchange feed.
Name the state you can actually observe
A missing field is structural: the row ends before the selected column. An empty field exists but contains no characters. A whitespace-only field contains spaces or tabs. Those distinctions help locate an export problem, but none alone explains why a price is absent.
A token such as NA or null has meaning only under a stated convention. The W3C tabular-data model distinguishes a cell’s original string from its interpreted value and allows metadata to identify null tokens. This checker uses your declared token list while keeping the source text. It does not assume that every publisher uses the same convention.
| Source state | Example | Review result |
|---|---|---|
| Field absent | Row ends before price | missing_field |
| Empty field | Comma followed by nothing | empty |
| Whitespace only | Three spaces | whitespace |
| Declared token | NA with NA configured | missing_token |
| Explicit zero | 0 or -0.00 | zero |
| Non-finite token | NaN or Infinity | non_finite |
Sources: W3C — Model for Tabular Data and Metadata on the Web.
Read the CSV structure before reading a number
CSV quoting determines where a field starts and ends. RFC 4180 describes quoted fields containing commas or line breaks and doubled quotation marks within quoted text. A comma inside a quoted value is therefore not automatically another column. A parser must establish the cells before a validator can classify them.
The original file and a decoded cell are different levels of evidence. CSV syntax quotes may disappear during parsing, while the decoded text remains. Keep the original file if you need to preserve quoting and line endings byte for byte. A review report of decoded values cannot reconstruct every formatting choice in that file.
Use the header names exactly, and resolve duplicate headers before continuing. If a row contains extra fields, keep a row-shape warning alongside its price classification. A short row should not be silently padded and then described as an originally empty price.
Sources: RFC Editor — RFC 4180, Common Format and MIME Type for CSV Files.
Do not infer the original database value from an empty export
A blank CSV cell may already have lost information upstream. Python’s standard CSV writer, for example, writes None as an empty string and explicitly documents that this transformation is not reversible. Its normal reader returns strings rather than automatically assigning every cell a numeric type.
That example is a reason to retain the exporter’s settings, not a claim that your file was produced by Python. If you cannot tell whether an empty cell once represented a database null or an intentional empty string, record that question as unresolved. Reopening the same CSV in another application cannot recover a distinction that was removed before export.
Sources: Python Documentation — CSV file reading and writing.
Zero is a class, not an approved price
The checker recognises signed decimal zero, including 0.000 and -0.00, without replacing its original spelling. An explicit zero is not missing. It may still be unsuitable for your chosen field, such as a strictly positive price. Record the value and investigate its meaning rather than disguising it as a blank.
A negative decimal is also kept and labelled separately. This worksheet does not apply one universal rule to every financial series. For a positive spot-price sample, negative values warrant investigation; for another kind of series, the domain rules may differ. The class describes syntax and sign, not economic validity.
Formats such as 1,000.50, $100 and 1e3 are left as invalid-number text under this checker’s deliberately narrow decimal rule. Convert them only in a separate, documented step if the source convention is known. Otherwise a locale guess can turn a separator into a different price.
A Singapore date boundary must not become a data gap
Keep the original UTC time and add the Singapore display in another field. Singapore uses UTC+08:00; the official BCA data-format document explicitly illustrates that offset. It is cited for the clock notation only, not as a market-data standard.
In the sample, 20 October 2026 at 15:59:59 UTC appears as 23:59:59 in Singapore. One second later, 16:00:00 UTC appears as 21 October at 00:00:00. The date label changes, but the two timestamps are still one second apart. A local-day grouping must not invent a missing row between them.
If the timestamp is blank or invalid, retain the price classification and flag timing separately. Do not borrow the previous row’s time, assume midnight, or use the computer’s current time. A valid price with an unknown observation time remains incomplete evidence.
Sources: Singapore BCA — Site Management Data Standards v1.1.
Count categories without manufacturing a price series
The seven-row example produces one positive decimal, one zero, one empty cell, one declared token, one non-finite token, one whitespace-only cell and one absent field. These are counts of input states. They are not seven valid prices, seven failed trades or evidence of an exchange outage.
Keep category totals separate from any later analysis. The checker deliberately supplies no average, return, candle or replacement price. If a later research task needs a complete series, specify its missing-data policy independently and preserve this inspection as the before-state. Do not quietly carry a previous observation across a gap.
Leave the next reader a precise unresolved question
A useful review row records the logical row number, decoded raw value, whether the field existed, classification, timestamp status and any shape warning. Add a local reference to the original file and a note such as “exporter null convention unknown”. Keep account identifiers and unrelated private fields out of a shareable sample.
The complete method works with a text editor and the classification table above. The browser checker makes that inspection quicker; it does not authenticate the file or repair its source. You can finish the review without following the optional Telegram offer below.
Worked examples
Example 1
- csv text
- timestamp_utc,price 2026-10-20T15:59:59Z,101.20 2026-10-20T16:00:00Z,0 2026-10-20T16:00:01Z, 2026-10-20T16:00:02Z,NA 2026-10-20T16:00:03Z,NaN 2026-10-20T16:00:04Z," " 2026-10-20T16:00:05Z
- price column
- price
- timestamp column
- timestamp_utc
- null tokens
- ['NA', 'null']
- row count
- 7
- classes
- ['positive', 'zero', 'empty', 'missing_token', 'non_finite', 'whitespace', 'missing_field']
- raw prices
- ['101.20', '0', '', 'NA', 'NaN', ' ', None]
- first timestamp sgt
- 2026-10-20T23:59:59+08:00
- second timestamp sgt
- 2026-10-21T00:00:00+08:00
- replacement count
- 0
Hypothetical rows. The absent final field is different from the present empty third price. No values are filled or dropped.
Example 2
- csv text
- timestamp_utc,price 2026-10-20T16:00:00Z,-0.00 2026-10-20T16:00:01Z,-1.25 2026-10-20T16:00:02Z,1e3
- price column
- price
- timestamp column
- timestamp_utc
- null tokens
- []
- row count
- 3
- classes
- ['zero', 'negative', 'invalid_number']
- raw prices
- ['-0.00', '-1.25', '1e3']
- replacement count
- 0
The explicit signed zero stays zero, the negative value stays separate, and scientific notation is not interpreted under the stated decimal-only rule.
Questions before you use the result
Can this tell why an observation is missing?
No. It shows what the supplied file contains. The cause requires exporter documentation or other source evidence.
Should I replace a missing price with the previous price?
This checker does not make that decision. Keep gaps visible and define any later transformation separately, including its effect on the analysis.
Is an empty cell the same as a null value?
Not necessarily. A particular exporter or schema may map one to the other, but the original distinction can be lost. Keep the convention with the file.
Primary sources and scope
- RFC Editor — RFC 4180, Common Format and MIME Type for CSV FilesChecked 2026-10-20. Quoted commas and line breaks, doubled quotes, field counts and spaces in CSV. This is format guidance, not a price-quality rule.
- W3C — Model for Tabular Data and Metadata on the WebChecked 2026-10-20. Distinguishes original string representation and semantic cell value; column metadata can define null tokens.
- Python Documentation — CSV file reading and writingChecked 2026-10-20. The normal reader returns strings. The writer documents that None-to-empty-string conversion is not reversible.
- Singapore BCA — Site Management Data Standards v1.1Checked 2026-10-20. Page 65 explicitly identifies +08:00 as Singapore Standard Time. Used only for date-time notation.
Prepared from the sources listed above. Examples and calculations explain a method; they do not predict prices or certify an investment. Geographic time references do not establish where a trading service is available. Dated schedules can change: consult the named publisher before relying on a future event.