Import each quantity with its unit and each monetary amount with its currency. Convert through a named, dated rule while retaining the original values. Add only quantities on a compatible basis, and reconcile converted totals separately from changes caused by price or exchange-rate assumptions.
Make the measurement basis part of the field
A column labelled amount is not a complete specification. Record whether it contains kilograms, grams, cases, hours or money, and whether the measurement is gross, net or adjusted. Two suppliers may both report weight while one includes packaging. A kilogram conversion will not resolve that conceptual difference. Describe the physical quantity before choosing a conversion factor or accepting an apparently standard column name.
For money, store a currency code rather than depending on a symbol such as a dollar sign. Also identify whether the figure includes tax, delivery charges or discounts. A unit belongs in the schema or a companion field that survives export. Formatting a cell as euros changes its appearance; it does not establish the source currency or calculate an exchange conversion.
Separate exact conversions from assumptions
Conversions between grams and kilograms use a defined scale. Conversions between cases and individual items require the pack size for that product and date. Record the conversion direction explicitly: kilograms equal grams divided by 1,000. Test one small and one large observation by hand. A wrong factor can produce values that still fall inside a broad accepted range, so format validation alone is weak protection.
Currency conversion needs a purpose, a rate source and a date convention. A planning comparison might use a fixed assumed rate, while a financial reporting process may require a different approved basis. Keep those uses distinguishable. Store source amount, source currency, rate, reporting currency and converted amount. Round at an agreed stage and show any reconciliation difference introduced by rounding rather than silently adjusting an unrelated row.
Reconcile within compatible groups first
Before producing a grand total, compare counts and subtotals by source unit and currency. An unexpected new unit should enter an exception queue, not inherit the last conversion seen. Check for double conversion when a file already contains reporting-currency amounts. Retain negative values where they represent genuine returns, but verify that their units and sign convention match the corresponding original transaction.
Expose important assumptions beside the report. Readers should know whether a change reflects more physical volume, a price change or currency translation. The government framework is further reading on data fitness; the worked import below is fictional and uses an invented exchange rate. It illustrates a reproducible calculation rather than prescribing an accounting rate or claiming that an external dataset has been inspected.
Check the dimensional relationship between related fields too. Quantity multiplied by unit price should reproduce the stated amount within the approved rounding tolerance. A successful conversion of weight cannot compensate for a unit price still expressed per gram rather than per kilogram.
Fictional import: stock weights and purchasing currencies
A fictional scenario to explain the method, not a customer result or a market benchmark.
A fictional workshop receives one line showing 12 kilograms of material and another showing 3,500 grams. The import retains both source quantities and converts the second to 3.5 kilograms. The compatible physical total is 15.5 kilograms. Adding the raw numbers would display 3,512, which has no coherent unit and could badly distort purchasing needs.
The same file contains costs of USD 120 and EUR 80. For an internal planning scenario only, the analyst assumes USD 1.10 per EUR. The second cost becomes USD 88, making the planning total USD 208. The original EUR 80 remains visible, and the assumed rate is labelled with its scenario date.
The reviewer checks the two conversions independently and declines to describe the monetary total as a statutory reporting value. Neither calculation establishes material quality, invoice accuracy or a real exchange rate. Those questions require their own evidence.
TAKE THIS INTO YOUR NEXT CONVERSATION
A practical next-step checklist.
- Record measurement meaning and source units.
- Use unambiguous currency codes.
- Retain original values beside conversions.
- Document rates, dates and rounding decisions.
Common questions.
Can I sum different currencies and label the result later?
No. Choose a reporting currency and apply an explicit conversion before adding the amounts.
Are two quantities labelled kilograms always comparable?
No. Packaging, moisture or other measurement conventions can differ even when the physical unit matches.
Further reading & source context.
These primary references provide background. They are not evidence for the fictional example above and do not endorse Accuralytix.
Found an error or a missing qualification? Send an editorial correction.
Check my data readiness.
Use a practical tool to turn this idea into a clearer starting point.
Check my data readiness