fs-merge

Financial Statement Merge (FS Merge)

What this skill does

Takes N year-by-year financial statement files (PDF or xlsx) provided by accounting/tax practitioners and creates a comparable merged xlsx with all years aligned in one sheet. The output is 재무제표/손익계산서/제조원가명세서 a format with 3 sections in a single sheet.

Two key constraints:

1. No automatic account-name matching. Errors are possible due to spacing/expression differences. Match sequentially based on row position, but verify with matching numbers for duplicate years.
2. Uncertain items go in a separate flag column. The user verifies manually.

Input file handling

Format	Reading method
.xlsx (organized FS)	Available spreadsheet reader or openpyxl read_only=True
.xlsx (DART download raw)	Same, but there may be multiple sheets — check each sheet
.pdf (text-based — normal extraction)	pdftotext -layout, pypdf, or an available PDF text extractor
.pdf (text-based — CID extraction failure)	After image conversion, OCR/visual reading (see ⚠ below)
.pdf (scanned copy)	scripts/pdf_to_images.py render pages as PNG → read the images directly via OCR/visual reading

Quickly determine whether a PDF is text-based:

pdftotext -f 1 -l 1 input.pdf - | head -20

Three result branches:

• Meaningful Korean text output → pdftotext use as-is
• Empty output + suspected scanned copy → pdf_to_images.py path
• Empty output + Syntax Error: Missing language pack for 'Adobe-Korea1' mapping or Unknown font tag 'CJK1' → CID encoding issue (see next ⚠)

⚠ CID Korean PDF (native text but extraction fails)

Korean PDFs issued by tax accountants/accounting firms often use CID fonts (Adobe-Korea1) with a missing ToUnicode CMap. In this case, pdftotextproduces empty output plus the above warning. Processing order:

1. Backup attempt: python -c "import pdfplumber; print(pdfplumber.open('x.pdf').pages[0].extract_text())" — works in some cases
2. Try PyMuPDF: python -c "import fitz; print(fitz.open('x.pdf').load_page(0).get_text())" — works in additional cases
3. If all fail, handle the same as a scanned copy: pdf_to_images.py render as PNG → read via OCR/visual reading

In this pattern, partial results often occur where only numbers are extracted and Korean account names disappear. Do not trust such results and retry using the image path.

When reading numbers from scanned copies/image-converted PDFs, cells with risk of digit conversionare explicitly recorded in the OCR_의심 flag.

Execution environment / dependencies

• scripts/build_workbook.py 는 Python 3 + openpyxl is required.
• scripts/pdf_to_images.py uses Python 3 + PyMuPDF (fitz) when PDF rendering is needed.
• If there are no dependencies, notify the user, and install only if package installation is permitted in the current project/sandbox.

Work procedure (6 steps)

Phase 1 — File inventory + period classification

Determine which fiscal year(s) each input file covers. At this point, also check the period span:

• BS: Only the balance sheet date needs to be checked (point in time)
• PL/MS: Check both the start date and end date. If less than 12 months, interim closing (semi-annual/quarterly) mark as

If the same year appears in multiple files, adopt the figures from the more recently disclosed FS(the most up-to-date information after reclassification/correction).

Inventory output

Organize in the following format:

파일                  | BS 일자       | PL 기간              | 비고
BSPL_2022.pdf         | 2022-12-31    | 2022-01-01~12-31    | 12M
BSPL_2023.pdf         | 2023-12-31    | 2023-01-01~12-31    | 12M (3기 비교)
                      | 2022-12-31    | 2022-01-01~12-31    |
BS_202506.pdf         | 2025-06-30    | —                   | 6기 중간결산 BS
                      | 2024-12-31    | —                   | 5기 비교
PL_202506.pdf         | —             | 2025-01-01~06-30    | 6M 중간결산 PL
                      | —             | 2024-01-01~12-31    | 12M 5기 비교

Phase 2 — Per-file extraction

From each file, extract (account name, actual account 0/1, value array) for PL · MS · BS. Do not merge at this stage — secure only raw data per file.

For scanned/CID PDFs, convert to images and then do visual reading. Process one page at a time and flag blurry numbers.

Phase 3 — Building the Master template

Adopt as the master the account order, names, actual account classification (0/1), and subtotal structure of the most recent FS exactly as they are. This becomes the row skeleton of the output xlsx.

Phase 4 — Sequential matching + duplicate year verification

The core of this skill. For the detailed algorithm, see references/matching_algorithm.md .

Summary:

• master (e.g., 5th-period FS) and older (e.g., 4th-period FS) both have 4th-period figures
• Advance the two pointers master_idx and older_idx simultaneously across leaf rows
• For each row, compare the 4th-period values:
  • Match → ordering OK, fill master with older's 3rd-period values
  • Small difference (±1 or ≤0.1%) → 재계산_차이 record flag and proceed
  • Large mismatch → try 1–2 row lookahead; if not found, 매칭_의심 flag and proceed
• Do not guess-match automatically. Defer all uncertainties to user verification via flags

⚠ Absence of overlap year (gap)

If there is no common year between the two FS files (e.g., 2023 FS file + 2025-06 interim closing file, with no FY2024 standalone file in between) numeric fingerprint verification is fundamentally impossible. Handling:

1. If a gap is detected, first ask the user: "Is there a separate FY2024 standalone FS file? If not, the 2024 comparison column can only be position-matched and cannot be cross-checked."
2. If they say there is none, automatically flag all leaf rows before the gap with 매칭_의심 flag(e.g., "FY2024 fingerprint verification impossible between 2023 ↔ 2025/06 — position matching only")
3. Also state the fact of gap handling in the final report

Account changes (rename / split / merge)

Do not automatically match changes in the chart of accounts by year (e.g., 매입채무 → 외상매입금, 현금및현금성자산 → 현금 + 보통예금 split). For the decision tree, see references/account_changes.md .

Phase 5 — Verification and Totaling

• Self-verify whether each subtotal row = the sum of its children (checks sum format)
• PL non-additive verification (must be included):
  • 상품매출원가 = 기초재고 + 당기매입 - 기말재고
  • Ⅲ.매출총이익 = Ⅰ.매출액 - Ⅱ.매출원가
  • Ⅴ.영업이익 = Ⅲ.매출총이익 - Ⅳ.판매비와관리비
  • Ⅷ.소득세차감전이익 = Ⅴ.영업이익 + Ⅵ.영업외수익 - Ⅶ.영업외비용
  • Ⅹ.당기순이익 = Ⅷ.소득세차감전이익 - Ⅸ.소득세등
• BS:
  • 자산총계 = 부채총계 + 자본총계 (automatically correct by structure)
  • 유동자산 = 당좌자산 + 재고자산, 비유동자산 = 투자+유형+무형+기타
• For manufacturing: PL 당기제품제조원가 (under II. Cost of Sales) = MS Ⅺ.당기제품제조원가 (show difference in Ref section)

The non-additive verification formula is expressed as a signed linear combination in the checks[].terms field (seereferences/output_layout.md ).

Equity roll-forward verification — conditional

기말 이익잉여금 = 시초 + 당기순이익 ± 기타자본변동 verification is performed only when the equity section has the following structure:

• Capital stock / retained earnings are separated into separate rows
• Opening and closing balances are clear

If a sole proprietor or small corporation treats equity as a single row (capital + profit + withdrawals lump-sum), skip this verification. Withdrawal information is not in the BS and only in the trial balance, so reconstruction is impossible.

Report verification failures to the user, but generate the output anyway (the user will judge based on the results).

Phase 6 — Output Build

scripts/build_workbook.py Pass the data as JSON input to generate the xlsx.

python scripts/build_workbook.py /tmp/payload.json <output-dir>/FS_합본.xlsx

See the build script docstring for the JSON schema. Key fields:

• fiscal_year_ends: Array of fiscal year-end dates as ISO strings (oldest first)
• period_notes (optional): fiscal_year_endsArray parallel to "6M interim" For periods that are not 12 months, use short labels such as null. Displayed as a separate row below the header.
• statements.PL / MS / BS: Array of row objects. Each row is account, is_leaf, values, flags
• statements.BS rows can add the group field (default is to leave it blank per user instruction)
• If a row object has is_section_header: true , leave the actual account (C) column blank (e.g., [자산]/[부채]/[자본] visual separator row)
• checks: Subtotal verification. Supports two formats:
  • child_indices: [a, b, c] — all-positive sum (legacy)
  • terms: [{idx, sign}, ...] — signed linear combination (for non-additive verification)
• refs: Cross-reference verification between statements

Subtasks are explicitly marked as is_leaf: false . Only rows with is_leaf: true are subject to pointer advancement in master/older matching.

For non-manufacturing (no MS), pass an empty array as "MS": [] — the build script skips the section itself.

For layout details, see references/output_layout.md .

Post-output reporting

Save the xlsx file to the output path specified by the user or accessible in the current execution environment, and deliver it to the user via that environment's file delivery/attachment method. Also summarize and report the following:

• Number of files processed and the fiscal periods/periods each covers (12M / 6M / 3M notation)
• Output year range
• Overlap-year fingerprint verifiable range / unverifiable gap range(e.g., "2022↔2023 verification OK, 2023↔2024 gap (no FY2024 standalone file)")
• Issues found during the matching stage (count per flag type and representative items)
• Items that failed sum verification (if any)
• If interim statements (non-12M) exist, separately emphasize cautions when comparing PL

Report as-is even if there are many flags. The user explicitly stated they will manually verify.

Things to never do

• Automatic lookup/merge by account name (string-based join)
• Arbitrarily resolving mismatches with heuristics (e.g., "the names are similar so they must be the same")
• Inserting/deleting rows without asking the user
• Attempting automatic classification of BS Group columns (NOA/NWC/Capex/Debt/Tax, etc.) — the user does this manually
• Adding colors, borders, or other styles to the output xlsx (the user prefers minimal)
• Attempting unit unification — if the source is in thousands of won/millions of won, keep it as-is and notify the user
• Simply comparing interim PL with 12M PL — the period must be specified in the column header
• Using partially extracted Korean PDF results as-is — if the Korean disappears and only numbers remain, retry via the image path

Ignore DART links

Even if a DART URL is received, do not fetch it directly. Ask the user to download the PDF/xlsx and attach it.

v2 changes (compared to v1)

• Specified the fallback procedure for cases where CID Korean PDF extraction fails
• Added handling procedure for when an overlap-year gap occurs (references/matching_algorithm.md)
• Interim statement (non-12M) detection and period_notes schema added
• Specified a standard checklist for non-additive PL verification formulas (gross profit/operating profit, etc.)
• Specified conditional application of equity roll-forward verification
• Account change (rename/split/merge) decision tree (references/account_changes.md new)
• checks[].terms Added signed linear combination schema
• is_section_header Added row option ([자산]/[부채]/[자본] 등)
• Specified the negative-input convention for contra accounts (accumulated depreciation, etc.) (references/output_layout.md)