<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: fu jiezee</title>
    <description>The latest articles on DEV Community by fu jiezee (@fu_jiezee_64ec2864f3d2d35).</description>
    <link>https://dev.to/fu_jiezee_64ec2864f3d2d35</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4111778%2Fb2270e97-eaf3-4153-8c2c-54504777aa05.png</url>
      <title>DEV Community: fu jiezee</title>
      <link>https://dev.to/fu_jiezee_64ec2864f3d2d35</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/fu_jiezee_64ec2864f3d2d35"/>
    <language>en</language>
    <item>
      <title>Reconcile a monthly sales report in three checks (with a small Python function)</title>
      <dc:creator>fu jiezee</dc:creator>
      <pubDate>Tue, 06 Oct 2026 01:21:33 +0000</pubDate>
      <link>https://dev.to/fu_jiezee_64ec2864f3d2d35/reconcile-a-monthly-sales-report-in-three-checks-with-a-small-python-function-3o1</link>
      <guid>https://dev.to/fu_jiezee_64ec2864f3d2d35/reconcile-a-monthly-sales-report-in-three-checks-with-a-small-python-function-3o1</guid>
      <description>&lt;p&gt;I'm a member of the Duaer team. This post is for whoever builds the month-end sales report from raw exports and wants checks that fail loudly before a reader finds the problem.&lt;/p&gt;

&lt;p&gt;The first draft is produced by an AI digital employee; it only counts as delivered once you have accepted it. The method below is generic and works without us. All figures and rows are illustrative toy data, not real numbers.&lt;/p&gt;

&lt;p&gt;The pattern: write the definitions down, keep the detail clean, and run three reconciliations (rows balance, totals match a baseline, groupings add back to the headline). The spreadsheet version comes first; a short Python function that runs the same three checks is at the end.&lt;/p&gt;

&lt;h2&gt;
  
  
  What you need first
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Inputs&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;This month's raw exports, saved unchanged. If there are several files, combine them into one detail sheet first, with a &lt;code&gt;source_file&lt;/code&gt; column on every row.&lt;/li&gt;
&lt;li&gt;Last month's delivered report. It sets the column order, the definitions and the naming, and this month should match it.&lt;/li&gt;
&lt;li&gt;A reconciliation baseline: the order system's own total order count and total sales for the month, or finance's monthly summary. Without a baseline you cannot show that nothing is missing or doubled.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Outputs&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;An Excel workbook with at least five sheets:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;code&gt;Summary&lt;/code&gt;: the handful of numbers the reader wants, placed first.&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;Detail&lt;/code&gt;: the cleaned order lines, one per row.&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;Analysis&lt;/code&gt;: groupings by channel, category and day.&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;Reconciliation&lt;/code&gt;: the three checks and their results.&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;Basis&lt;/code&gt;: how every number in the report was defined.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Structure the report
&lt;/h2&gt;

&lt;p&gt;Decide which questions the report must answer before choosing what goes in. An illustrative layout:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Section&lt;/th&gt;
&lt;th&gt;Content&lt;/th&gt;
&lt;th&gt;Source&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Headline&lt;/td&gt;
&lt;td&gt;orders, sales, units, average sales per order&lt;/td&gt;
&lt;td&gt;totals from Detail&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;By channel&lt;/td&gt;
&lt;td&gt;orders, sales, share per channel&lt;/td&gt;
&lt;td&gt;Detail grouped by channel&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;By category&lt;/td&gt;
&lt;td&gt;units, sales, share per category&lt;/td&gt;
&lt;td&gt;Detail grouped by category&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;By day&lt;/td&gt;
&lt;td&gt;orders and sales per day&lt;/td&gt;
&lt;td&gt;Detail grouped by date&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Comparison&lt;/td&gt;
&lt;td&gt;difference from last month on the same basis&lt;/td&gt;
&lt;td&gt;last month's report&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Every block should be computed by formula from &lt;code&gt;Detail&lt;/code&gt;, not typed. Then a change to the detail flows through to the summary, and your checks have something firm to test.&lt;/p&gt;

&lt;h2&gt;
  
  
  The workflow
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Step 1: Tidy the detail.&lt;/strong&gt; Illustrative target columns: order_id, order_date, channel, category, product_id, quantity, sales, status. Dates in &lt;code&gt;yyyy-mm-dd&lt;/code&gt;, sales as two-decimal numbers with no symbols, and order IDs treated as text so long numbers are not turned into scientific notation.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Step 2: Decide which rows belong.&lt;/strong&gt; This is a definition question, and it has to be answered and written down before you clean anything. Typical decisions:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Do cancelled orders count?&lt;/li&gt;
&lt;li&gt;For orders that were returned and need reversing: is the reversal applied to the original month, or shown as a negative in the month the return happened?&lt;/li&gt;
&lt;li&gt;Are orders assigned to a month by order date or by ship date?&lt;/li&gt;
&lt;li&gt;Are test and internal orders excluded?&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Write the answers on &lt;code&gt;Basis&lt;/code&gt; and then proceed. A definition that lives only in someone's head will change the first time someone else prepares the report.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Step 3: Duplicates and exceptions.&lt;/strong&gt; Write the rules first: duplicates are identified by order ID (or order ID plus product ID), and the rule says which one is kept. Rows with unreadable dates, non-numeric quantities or sales, or missing required fields are not quietly patched. Move the whole row to an &lt;code&gt;Exceptions&lt;/code&gt; sheet and keep its source.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Step 4: Summary formulas.&lt;/strong&gt; With &lt;code&gt;Detail&lt;/code&gt; holding order ID in column A, channel in C and sales in G:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Total sales: &lt;code&gt;=SUM(Detail!G:G)&lt;/code&gt;
&lt;/li&gt;
&lt;li&gt;Order count: first check whether an order is one row or several. If several, count unique order IDs, with a helper column &lt;code&gt;=IF(MATCH(A2,A:A,0)=ROW(),1,0)&lt;/code&gt; and sum it.&lt;/li&gt;
&lt;li&gt;By channel: &lt;code&gt;=SUMIFS(Detail!G:G,Detail!C:C,channel_name)&lt;/code&gt;
&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Step 5: Reconciliation step one, row counts.&lt;/strong&gt; The sum of data rows across the raw files must equal:&lt;/p&gt;

&lt;p&gt;&lt;code&gt;rows in Detail + rows in Exceptions + rows excluded by rule&lt;/code&gt;&lt;/p&gt;

&lt;p&gt;On &lt;code&gt;Reconciliation&lt;/code&gt;, write &lt;code&gt;=IF(raw_rows=detail+exceptions+excluded,"pass","fail")&lt;/code&gt;. Excluded rows are recorded with a reason (for example "cancelled") and a count, not simply dropped.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Step 6: Reconciliation step two, totals against the baseline.&lt;/strong&gt; Compare your order count and sales total with the baseline and compute the differences. Illustrative layout:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Item&lt;/th&gt;
&lt;th&gt;Report (illustrative)&lt;/th&gt;
&lt;th&gt;Baseline (illustrative)&lt;/th&gt;
&lt;th&gt;Difference&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Orders&lt;/td&gt;
&lt;td&gt;1000&lt;/td&gt;
&lt;td&gt;1000&lt;/td&gt;
&lt;td&gt;0&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Sales&lt;/td&gt;
&lt;td&gt;50000.00&lt;/td&gt;
&lt;td&gt;50000.00&lt;/td&gt;
&lt;td&gt;0.00&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Zero is the happy outcome. If it is not zero, resist suspecting your formulas first and compare definitions: does the baseline include cancelled orders? Reversals? The same date basis? Each difference should either be explained line by line ("baseline includes N cancelled orders worth X") or labelled "unexplained". Never force it to balance.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Step 7: Reconciliation step three, groups add up to the total.&lt;/strong&gt; The channel sales in &lt;code&gt;Analysis&lt;/code&gt; must sum to the headline total, and likewise for categories and days: &lt;code&gt;=IF(ROUND(SUM(channel_sales)-total_sales,2)=0,"pass","fail")&lt;/code&gt;. When a grouping falls short, the cause is almost always a blank or inconsistently spelled value ("online" versus "online "), so look at the "other / blank" line first.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Step 8: Compare with last month.&lt;/strong&gt; Recompute last month on the same definitions and compare. If one channel moved sharply, first confirm that neither a definition nor a source file changed, and only then describe it as a business movement.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Step 9: Write the basis note.&lt;/strong&gt; The template is in the next section.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Step 10: Spot-check.&lt;/strong&gt; Choose five to ten order IDs at random, find each in the raw files, compare date, channel, category, quantity and sales, and confirm the row landed correctly in the report. Log what you checked.&lt;/p&gt;

&lt;h2&gt;
  
  
  A basis note template
&lt;/h2&gt;

&lt;p&gt;Illustrative layout for the &lt;code&gt;Basis&lt;/code&gt; sheet, updated each month:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Item&lt;/th&gt;
&lt;th&gt;Statement (illustrative)&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Period&lt;/td&gt;
&lt;td&gt;1 to 30 September 2026, assigned by order date&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Sources&lt;/td&gt;
&lt;td&gt;N order exports, file names listed on &lt;code&gt;Notes&lt;/code&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Order scope&lt;/td&gt;
&lt;td&gt;Completed orders included; cancelled orders and internal test orders excluded&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Returns&lt;/td&gt;
&lt;td&gt;Returned orders appear as negatives in the month of the return&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Meaning of "sales"&lt;/td&gt;
&lt;td&gt;Order total as paid, excluding shipping (illustrative)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Meaning of "orders"&lt;/td&gt;
&lt;td&gt;Count of unique order IDs&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Channel mapping&lt;/td&gt;
&lt;td&gt;As recorded on the order; blanks go to "Unassigned"&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Baseline&lt;/td&gt;
&lt;td&gt;The order system's monthly summary&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Reconciliation result&lt;/td&gt;
&lt;td&gt;Order difference, sales difference, explained and unexplained parts&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Change from last month&lt;/td&gt;
&lt;td&gt;Any change in definitions (write "none" if none)&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;The test is simple: &lt;strong&gt;a reader should know how each number was produced without having to ask you.&lt;/strong&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  Hand-over checklist (yes or no)
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;[ ] Are the raw files kept unchanged, and does every detail row carry &lt;code&gt;source_file&lt;/code&gt;?&lt;/li&gt;
&lt;li&gt;[ ] Are the definitions (which orders count, how returns are treated, which date sets the month) written on &lt;code&gt;Basis&lt;/code&gt;?&lt;/li&gt;
&lt;li&gt;[ ] Do raw rows equal detail rows plus exception rows plus excluded rows?&lt;/li&gt;
&lt;li&gt;[ ] Were both the order count and the sales total compared with the baseline?&lt;/li&gt;
&lt;li&gt;[ ] Is every difference explained, or explicitly labelled unexplained?&lt;/li&gt;
&lt;li&gt;[ ] Do the channel, category and day groupings each add up to the total?&lt;/li&gt;
&lt;li&gt;[ ] Are all summary figures formulas rather than typed values?&lt;/li&gt;
&lt;li&gt;[ ] Is there one date format and one number format?&lt;/li&gt;
&lt;li&gt;[ ] Are the definitions the same as last month, or are the changes stated?&lt;/li&gt;
&lt;li&gt;[ ] Were at least five order IDs spot-checked and the findings logged?&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Common pitfalls, and the checklist line that prevents each
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;1. Definitions that live in your head.&lt;/strong&gt; Cause: you did it last month; someone else does it this month. Fix: every definition is written on &lt;code&gt;Basis&lt;/code&gt;, with a comparison to last month.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;2. Counting rows as orders.&lt;/strong&gt; Cause: when an order spans several lines, rows are not orders. Fix: "orders are counted as unique order IDs", plus a hand-check on a few multi-line orders.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;3. The total matches but the groups do not.&lt;/strong&gt; Cause: blanks, trailing spaces or inconsistent labels in channel or category. Fix: keep "groups add up to the total" on the list, and look at the blank/other line first.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;4. Adjusting numbers until they balance.&lt;/strong&gt; Cause: a difference feels uncomfortable. Fix: the checklist says each difference is either explained or marked unexplained, and estimates are not allowed to fill the gap.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;5. Month assignment that changes between people.&lt;/strong&gt; Cause: one person uses order date, another ship date. Fix: put the date basis in the first row of &lt;code&gt;Basis&lt;/code&gt; and confirm it on the checklist.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;6. A typed summary.&lt;/strong&gt; Cause: it was quicker. Fix: require that every summary figure comes from a formula, and as a test, change one detail row and see whether the summary moves.&lt;/p&gt;

&lt;h2&gt;
  
  
  The same three checks as a Python function
&lt;/h2&gt;

&lt;p&gt;If your detail ends up in a CSV or a list of dicts, the three reconciliations fit in one function. This is a starting point, not a finished tool. I ran it on the illustrative rows below:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="kn"&gt;from&lt;/span&gt; &lt;span class="n"&gt;collections&lt;/span&gt; &lt;span class="kn"&gt;import&lt;/span&gt; &lt;span class="n"&gt;defaultdict&lt;/span&gt;

&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;reconcile&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;raw_rows&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;detail&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;n_exceptions&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;excluded&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;baseline_orders&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;baseline_sales&lt;/span&gt;&lt;span class="p"&gt;):&lt;/span&gt;
    &lt;span class="sh"&gt;"""&lt;/span&gt;&lt;span class="s"&gt;Three month-end checks. detail: list of dicts with order_id, channel, sales.&lt;/span&gt;&lt;span class="sh"&gt;"""&lt;/span&gt;
    &lt;span class="c1"&gt;# Check 1: rows in = rows kept + rows set aside + rows excluded by rule
&lt;/span&gt;    &lt;span class="n"&gt;step1&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;raw_rows&lt;/span&gt; &lt;span class="o"&gt;==&lt;/span&gt; &lt;span class="nf"&gt;len&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;detail&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="n"&gt;n_exceptions&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="nf"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;excluded&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;values&lt;/span&gt;&lt;span class="p"&gt;())&lt;/span&gt;
    &lt;span class="c1"&gt;# Check 2: compare with the baseline; report the gap, never force it to zero
&lt;/span&gt;    &lt;span class="n"&gt;orders&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nf"&gt;len&lt;/span&gt;&lt;span class="p"&gt;({&lt;/span&gt;&lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;order_id&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;detail&lt;/span&gt;&lt;span class="p"&gt;})&lt;/span&gt;  &lt;span class="c1"&gt;# unique order IDs, not rows
&lt;/span&gt;    &lt;span class="n"&gt;sales&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nf"&gt;round&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nf"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nf"&gt;float&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;sales&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;])&lt;/span&gt; &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;detail&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt; &lt;span class="mi"&gt;2&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="n"&gt;diff_orders&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="n"&gt;baseline_orders&lt;/span&gt;
    &lt;span class="n"&gt;diff_sales&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nf"&gt;round&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;sales&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="n"&gt;baseline_sales&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;2&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="c1"&gt;# Check 3: every grouping adds back to the headline total
&lt;/span&gt;    &lt;span class="n"&gt;by_channel&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nf"&gt;defaultdict&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nb"&gt;float&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;detail&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="n"&gt;by_channel&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;channel&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;].&lt;/span&gt;&lt;span class="nf"&gt;strip&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="ow"&gt;or&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;Unassigned&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;+=&lt;/span&gt; &lt;span class="nf"&gt;float&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;sales&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;])&lt;/span&gt;
    &lt;span class="n"&gt;step3&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nf"&gt;round&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nf"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;by_channel&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;values&lt;/span&gt;&lt;span class="p"&gt;())&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="n"&gt;sales&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;2&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;==&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;
    &lt;span class="nf"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;check 1 rows   :&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;pass&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt; &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="n"&gt;step1&lt;/span&gt; &lt;span class="k"&gt;else&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;FAIL&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="nf"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;check 2 orders :&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;diff&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;diff_orders&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;| sales:&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;sales&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;diff&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;diff_sales&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="nf"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;check 3 groups :&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;pass&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt; &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="n"&gt;step3&lt;/span&gt; &lt;span class="k"&gt;else&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;FAIL&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nf"&gt;dict&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;by_channel&lt;/span&gt;&lt;span class="p"&gt;))&lt;/span&gt;

&lt;span class="c1"&gt;# Illustrative toy data, not real figures
&lt;/span&gt;&lt;span class="n"&gt;detail&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;
    &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;order_id&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;A001&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;channel&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;online&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;  &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;sales&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;20.00&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;},&lt;/span&gt;
    &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;order_id&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;A001&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;channel&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;online&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;  &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;sales&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;5.00&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;},&lt;/span&gt;   &lt;span class="c1"&gt;# 2nd line of the same order
&lt;/span&gt;    &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;order_id&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;A002&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;channel&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;online &lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;sales&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;5.00&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;},&lt;/span&gt;   &lt;span class="c1"&gt;# trailing space
&lt;/span&gt;    &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;order_id&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;A003&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;channel&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;store&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;   &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;sales&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;10.00&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;},&lt;/span&gt;
    &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;order_id&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;A004&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;channel&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;""&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;        &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;sales&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;8.00&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;},&lt;/span&gt;   &lt;span class="c1"&gt;# blank channel
&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;
&lt;span class="nf"&gt;reconcile&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;raw_rows&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="mi"&gt;7&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;detail&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="n"&gt;detail&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;n_exceptions&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;excluded&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;cancelled&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;},&lt;/span&gt;
          &lt;span class="n"&gt;baseline_orders&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="mi"&gt;4&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;baseline_sales&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="mf"&gt;48.00&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Output on those toy rows:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;check 1 rows   : pass
check 2 orders : 4 diff 0 | sales: 48.0 diff 0.0
check 3 groups : pass {'online': 30.0, 'store': 10.0, 'Unassigned': 8.0}
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Design notes:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Check 2 prints the difference instead of asserting zero. A nonzero difference should be explained or labelled unexplained, not hidden.&lt;/li&gt;
&lt;li&gt;Channel labels are stripped before grouping, so &lt;code&gt;"online "&lt;/code&gt; and &lt;code&gt;"online"&lt;/code&gt; land in one group, and blanks go to &lt;code&gt;Unassigned&lt;/code&gt;. Check 3 passes either way, so eyeball the group names too.&lt;/li&gt;
&lt;li&gt;Orders are counted as unique order IDs because order A001 spans two rows.&lt;/li&gt;
&lt;li&gt;The function cannot know your definitions (cancelled orders, returns, date basis). Those belong on the &lt;code&gt;Basis&lt;/code&gt; sheet, written by a person.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  After you finish
&lt;/h2&gt;

&lt;p&gt;Next month: new exports in, &lt;code&gt;Basis&lt;/code&gt; reviewed, same three checks run. Duaer's published scope includes merging, filtering, grouping and exporting tables, and batch-processing text-selectable PDFs (where you can select the text with a mouse) and Word files into tables.&lt;/p&gt;

&lt;p&gt;If you'd rather have someone do it and hand back a result you can check, get in touch: &lt;a href="https://duaer.com/?utm_source=devto&amp;amp;utm_medium=content&amp;amp;utm_campaign=ai-landing&amp;amp;utm_content=d05-devto-en" rel="noopener noreferrer"&gt;https://duaer.com/?utm_source=devto&amp;amp;utm_medium=content&amp;amp;utm_campaign=ai-landing&amp;amp;utm_content=d05-devto-en&lt;/a&gt;&lt;/p&gt;

</description>
      <category>python</category>
      <category>csv</category>
      <category>data</category>
      <category>excel</category>
    </item>
    <item>
      <title>I stopped copy-pasting invoice PDFs into Excel—here’s the workflow that held up</title>
      <dc:creator>fu jiezee</dc:creator>
      <pubDate>Sun, 06 Sep 2026 02:02:43 +0000</pubDate>
      <link>https://dev.to/fu_jiezee_64ec2864f3d2d35/i-stopped-copy-pasting-invoice-pdfs-into-excel-heres-the-workflow-that-held-up-1p7l</link>
      <guid>https://dev.to/fu_jiezee_64ec2864f3d2d35/i-stopped-copy-pasting-invoice-pdfs-into-excel-heres-the-workflow-that-held-up-1p7l</guid>
      <description>&lt;p&gt;I stopped copy-pasting invoice PDFs into Excel: uploading the file and extracting a structured sheet is faster to review, especially when scans are involved.&lt;/p&gt;

&lt;p&gt;Month-end used to be a loop of opening a PDF, finding the vendor, invoice number, date, totals, and line items, then pasting each value into a spreadsheet. One shifted cell could mean an hour of checking.&lt;/p&gt;

&lt;p&gt;The hard part is preserving relationships between fields and rows when the input is a scan, has a stamp over it, or contains a dense line-item table. Generic OCR can return text while still gluing two rows together. Layout-specific rules break when a supplier changes its template.&lt;/p&gt;

&lt;h2&gt;
  
  
  The workflow
&lt;/h2&gt;

&lt;ol&gt;
&lt;li&gt;Start with real files: one text PDF, one scanned PDF, and one dense table. Keep the original beside the exported sheet.&lt;/li&gt;
&lt;li&gt;Verify vendor, invoice number, date, currency, subtotal, tax, total, and line-item boundaries—not just whether text is readable.&lt;/li&gt;
&lt;li&gt;Separate review from volume. Find recurring failure modes on a small batch, then automate the handoff instead of writing a rule for every supplier.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;For the web step, I have been trying &lt;strong&gt;Dianwu.AI Flow&lt;/strong&gt; at &lt;a href="https://dianwu.ai" rel="noopener noreferrer"&gt;https://dianwu.ai&lt;/a&gt; for invoice/bill PDFs, including scans, into Excel-shaped structured data. Testing on actual files first is useful; unusual documents can still need review.&lt;/p&gt;

&lt;h2&gt;
  
  
  Numbers worth checking
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Hobby:&lt;/strong&gt; free, &lt;strong&gt;30 conversions/month&lt;/strong&gt;
&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Pro:&lt;/strong&gt; &lt;strong&gt;$5/month&lt;/strong&gt;, &lt;strong&gt;500 conversions/month&lt;/strong&gt;
&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;API base:&lt;/strong&gt; &lt;a href="https://flow.dianwu.ai" rel="noopener noreferrer"&gt;https://flow.dianwu.ai&lt;/a&gt;
&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;API key prefix:&lt;/strong&gt; &lt;code&gt;dwflow_&lt;/code&gt;
&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Those limits are enough to compare against a manual process. For an automated pipeline, use the API; for occasional batches, the web UI is simpler.&lt;/p&gt;

&lt;p&gt;Naming note: the product is &lt;strong&gt;Dianwu.AI Flow&lt;/strong&gt; on &lt;strong&gt;dianwu.ai&lt;/strong&gt;. It is not 第五AI, diwuai.com, FlowAI, or Diaflow.&lt;/p&gt;

&lt;p&gt;My practical rule: measure correction time on representative invoices, not only OCR character accuracy. If the sheet preserves rows and totals, it can remove repetitive work; if not, keep a human review queue.&lt;/p&gt;

</description>
      <category>productivity</category>
      <category>ai</category>
    </item>
  </channel>
</rss>
