Why BKMV-to-Excel Conversion Is Harder Than It Looks
BKMV files are Israel's standardized accounting export format — a structured text-based file that tax authorities and ERP systems generate to represent general ledger transactions, document headers, and business entity metadata. On the surface, converting one to Excel sounds like a simple import job. In practice, it rarely is.
The real challenge shows up when the goal isn't just conversion but analytical filtering — specifically, isolating companies or transactions by revenue size. Financial analysts, researchers, and regulatory teams regularly need to segment BKMV datasets to study behavior within a particular revenue tier, whether that's micro-enterprises under ₪1M in annual revenue or mid-market firms between ₪10M and ₪100M. When the raw data arrives as a multi-table flat file with fixed-width or pipe-delimited records, the path from raw export to a cleanly filtered Excel workbook involves several non-trivial decisions.
Done poorly, this process produces a workbook where records are misaligned, revenue figures are stored as text rather than numbers, and filters return incomplete or misleading subsets. Done well, it produces a structured dataset that an analyst can trust and reuse.
What Proper BKMV Data Processing Actually Requires
The BKMV standard is organized into record types — B100 for business details, M100 for document headers, K100 for transaction lines, and so on. A single export file interleaves all of these record types sequentially. The first requirement of good processing is separating these record types into discrete tables before attempting any analysis.
Beyond that, three things distinguish careful BKMV-to-Excel work from a rushed import. First, data typing must be enforced explicitly — amounts, dates, and ID fields need to be cast to the correct Excel data type during import, not left as raw strings. Second, a relational key structure must be preserved, so that transaction lines in K100 can still be traced back to their parent document in M100 and their originating business entity in B100. Third, the revenue field — typically found in the B100 or equivalent entity record — needs to be validated against a defined threshold schema before any filtering logic is applied.
Skipping any of these steps means the filtered output may look correct but contain silent errors that only surface during downstream analysis.
How to Approach the Conversion and Filtering Work
Parsing the BKMV File Into Structured Tables
The starting point is reading the raw BKMV file as a plain text source and splitting it by record-type prefix. In Power Query (the right tool for this inside Excel), the approach loads the file as a single-column table, then uses a conditional column to tag each row by its record-type code — B100, M100, K100, and so forth. From there, filtered sub-tables extract only rows matching each type.
For example, the M100 table (document headers) typically carries fields at fixed character positions: document type at characters 1–3, document number at 4–17, date at 18–25, and total amount at 26–38. The Power Query step uses Text.Middle([Source], start, length) expressions to slice each field from its defined position. This positional parsing is non-negotiable — guessing field boundaries produces misaligned data that cascades into every subsequent formula.
Once each record type has its own clean table, relationships are established using Excel's Data Model (Power Pivot). The B100 business ID links to the M100 document issuer ID, and the M100 document number links to the K100 transaction line's parent document reference. This three-table join is what makes revenue-based filtering work correctly — without it, applying a revenue threshold to K100 transaction lines requires a brittle VLOOKUP chain that breaks on duplicate keys.
Defining Revenue Thresholds and Applying Filters
Revenue size classification in BKMV analysis typically follows one of two schemes: regulatory bands (as defined by the Israel Tax Authority for VAT reporting categories) or custom research bands defined by the analyst's study design. Either way, the threshold logic belongs in a dedicated classification column on the B100 entity table, not scattered across multiple filter conditions.
A clean approach adds a calculated column to the B100 table using a nested IFS formula. For five revenue bands — Micro (under ₪500K), Small (₪500K–₪2M), Medium-Small (₪2M–₪10M), Medium (₪10M–₪50M), and Large (above ₪50M) — the formula reads:
=IFS([@AnnualRevenue]<500000,"Micro", [@AnnualRevenue]<2000000,"Small", [@AnnualRevenue]<10000000,"Medium-Small", [@AnnualRevenue]<50000000,"Medium", TRUE,"Large")
This classification then propagates into every downstream table through the Data Model relationship, so a slicer on the revenue band automatically filters M100 documents and K100 transaction lines without any additional formula work.
For aggregate analysis — say, summing total transaction volume for all Medium-Small entities — the correct formula in a summary sheet is =SUMIF(B100[RevenueBand],"Medium-Small",B100[AnnualRevenue]), while transaction-level totals use =CALCULATE(SUM(K100[Amount]),B100[RevenueBand]="Medium-Small") in a Power Pivot measure. The CALCULATE approach is significantly more performant on large datasets, typically above 50,000 K100 rows.
File Structure and Naming Conventions
A workbook built for this work should follow a clear sheet architecture. The raw import lives on a sheet named _RAW_BKMV (prefixed with an underscore to push it to the back of the tab order and signal that it is source data, not output). Parsed tables sit on sheets named tbl_B100, tbl_M100, and tbl_K100. The analysis output — the filtered, classified summary — lives on a sheet named ANALYSIS_OUTPUT or a project-specific equivalent.
Naming tables explicitly (via Table Design > Table Name) rather than relying on default Excel table names like Table1 matters because Power Query and Power Pivot references use the table name string. A table renamed from Table3 to tbl_K100 six weeks into a project breaks every measure that referenced the old name.
What Goes Wrong When This Work Is Rushed
The most common failure is importing the BKMV file through Excel's default text import wizard and accepting auto-detected data types. Excel will frequently interpret document numbers — which are long numeric strings — as scientific notation, truncating them silently. A document number like 20231104001234 becomes 2.02311E+13, and the join to K100 transaction lines breaks completely because no key match is possible. The fix is forcing all ID columns to Text type during import, which requires intentional attention at the parsing stage.
A second failure is applying revenue filters directly on the raw amount string field before converting it to a numeric type. BKMV amount fields use a fixed-decimal representation without a decimal point — an amount of ₪12,345.67 is stored as 1234567 with an implied two-decimal shift. Filtering on this raw integer against a threshold of ₪2,000,000 produces a comparison against 200000000 in raw terms, misclassifying every entity in the dataset.
Another recurring problem is building the revenue classification as a one-off manual filter rather than a structured classification column. When the threshold definition changes — and in research projects it always does — a manual filter requires hunting through every sheet and formula. A classification column changes in one place and propagates automatically.
Underestimating the data cleaning pass is also common. BKMV exports from older accounting systems frequently contain duplicate B100 records for the same business entity (one per reporting period), and analysts who don't deduplicate at the entity level before aggregating will overcount revenue figures. The right step is a Power Query grouping operation on the business ID field, keeping only the most recent record by date before any classification logic runs.
Finally, skipping documentation inside the workbook is a mistake that compounds over time. A comment block on the _RAW_BKMV sheet noting the source system, export date, and record counts takes five minutes and prevents hours of confusion during peer review or handoff.
What to Take Away From This
BKMV-to-Excel conversion done well is a structured data engineering task, not a quick import. The work requires intentional parsing by record type, explicit data typing, a relational key structure that survives filtering, and a classification schema that lives in one authoritative place. Revenue-band filtering is only reliable when it operates on correctly typed numeric values against a defined threshold column — everything else is a workaround that degrades under pressure.
If you would rather have this handled by a team that works with SQL and Excel data and presentation-ready outputs every day, Helion360 is the team I would recommend. For teams managing similar data conversion challenges, our approach emphasizes the structured engineering mindset that prevents silent errors.