Finans raporlamada Excel tabanlı kalite kontrol: veri standartları ve denetim izi

Finans raporlamasında Excel'in yerini hiçbir araç tam olarak almadı; bütçe, nakit akışı, gider dağılımı ve dönem kapanışı hâlâ büyük ölçüde çalışma kitaplarında üretiliyor. Sorun aracın kendisi değil, aynı hataların her dönem yeniden ortaya çıkması: metin olarak yapışmış tutarlar, farklı yazılmış hesap kodları, boş bırakılmış maliyet merkezi, iki kez yapıştırılan satır, son satırı kapsamayan bir toplam formülü. Bu hataların ortak özelliği raporun sonunda değil, başında, veri girildiği anda doğmalarıdır.

Bu yüzden kalite kontrolü "raporu daha dikkatli okumak" olarak değil, kaynağa yerleştirilmiş bir dizi otomatik kontrol olarak kurmayı tercih ederim. Girdi doğrulama hatayı içeri sokmaz, mutabakat kontrolleri toplamların birbirini tutmadığını sayı olarak gösterir, sürüm ve denetim izi hangi rakamın hangi dosyadan geldiğini kanıtlar, kontrol listesi de bütün bunları her dönem aynı sırayla işletir.

Bu yazıda bu dört katmanı bir çalışma kitabına ve birkaç kısa Python betiğine nasıl oturtmayı önerdiğimi anlatıyorum. Örneklerdeki dosya adları, kodlar ve tutarlar tamamen kurgusaldır; amaç desenin kendisini göstermek.

Veri standardı ve girdi doğrulama

Doğrulanacak bir kural yoksa doğrulama da yoktur. Kontrol yazmadan önce her girdi sayfası için kısa bir veri standardı belirlerim; bir sayfalık bu sözleşme, kalan her şeyin referansıdır:

• Sütun adları sabittir. "Tutar", "TUTAR" ve "Tutar (TL)" aynı sütun değildir; başlık satırı değişirse betik durur.
• Her sütunun tek bir tipi vardır. Tutar sayıdır, tarih tarihtir, hesap kodu metindir. Excel'in "genel" biçimi bu ayrımı korumadığı için tipi betik kontrol eder.
• Kod alanları kalıba uyar. Hesap kodu için ^\d{3}\.\d{2}\.\d{3}$, maliyet merkezi için ^MM-\d{4}$ gibi açıkça yazılmış bir düzenli ifade tanımlanır.
• Referans listeleri tek yerden gelir. Para birimi, maliyet merkezi, hesap planı gibi listeler ayrı bir "Referans" sayfasında durur; girdi sayfasındaki değerler buradan doğrulanır.
• Zorunlu alanlar boş kalamaz. Bir satırın raporlanabilmesi için hangi alanların dolu olması gerektiği yazılıdır.

Bu standardı Excel'in kendi araçlarıyla da uygulatırım: veri doğrulama listeleri, korunan başlık satırı, yapılandırılmış tablolar. Ama Excel tarafındaki korumalar kopyala-yapıştır ile kolayca aşılır; asıl bekçi dışarıdan çalışan betiktir. Kuralları kod düzeyinde nasıl sözleşmeye dönüştürdüğümü veri sözleşmesi ve şema doğrulama yazısında ayrıntılı anlattım. Aşağıdaki örnek, bir gider sayfasını pandas ile okuyup standarda göre denetler. Her ihlal satır numarası ve nedenle birlikte listeye düşer; betik hiçbir şeyi kendiliğinden düzeltmez, yalnızca raporlar.

import re
import pandas as pd

ZORUNLU = ["belge_no", "tarih", "hesap_kodu", "maliyet_merkezi", "tutar", "para_birimi"]
HESAP_KALIBI = re.compile(r"^\d{3}\.\d{2}\.\d{3}$")
MM_KALIBI = re.compile(r"^MM-\d{4}$")


def girdi_dogrula(dosya, sayfa="Giderler", referans="Referans"):
    df = pd.read_excel(dosya, sheet_name=sayfa, dtype=str)
    ref = pd.read_excel(dosya, sheet_name=referans, dtype=str)
    gecerli_pb = set(ref["para_birimi"].dropna())
    bulgular = []

    eksik = [k for k in ZORUNLU if k not in df.columns]
    if eksik:
        return [(1, f"eksik sütun: {', '.join(eksik)}")]

    for i, satir in df.iterrows():
        excel_satir = i + 2  # başlık satırı 1

        bos = [k for k in ZORUNLU if pd.isna(satir[k]) or str(satir[k]).strip() == ""]
        if bos:
            bulgular.append((excel_satir, f"boş alan: {', '.join(bos)}"))
            continue

        if not HESAP_KALIBI.match(satir["hesap_kodu"]):
            bulgular.append((excel_satir, f"hesap_kodu kalıba uymuyor: {satir['hesap_kodu']}"))
        if not MM_KALIBI.match(satir["maliyet_merkezi"]):
            bulgular.append((excel_satir, f"maliyet_merkezi kalıba uymuyor: {satir['maliyet_merkezi']}"))
        if satir["para_birimi"] not in gecerli_pb:
            bulgular.append((excel_satir, f"para_birimi referansta yok: {satir['para_birimi']}"))

        try:
            tutar = float(satir["tutar"].replace(",", "."))
            if tutar < 0:
                bulgular.append((excel_satir, "tutar negatif"))
        except ValueError:
            bulgular.append((excel_satir, f"tutar sayı değil: {satir['tutar']}"))

        if pd.to_datetime(satir["tarih"], format="%Y-%m-%d", errors="coerce") is pd.NaT:
            bulgular.append((excel_satir, f"tarih ISO biçiminde değil: {satir['tarih']}"))

    tekrar = df[df.duplicated("belge_no", keep=False)]
    for i in tekrar.index:
        bulgular.append((i + 2, f"belge_no tekrar ediyor: {df.at[i, 'belge_no']}"))

    return sorted(bulgular)


if __name__ == "__main__":
    for satir, neden in girdi_dogrula("ORNEK-2024-01-giderler.xlsx"):
        print(f"satır {satir}: {neden}")

Betik dtype=str ile okur; böylece Excel'in kendi tip tahmini devreye girmez ve "metin olarak saklanmış sayı" gibi durumlar gizlenmez. Bulgu listesi boşsa dosya bir sonraki aşamaya geçer; doluysa listeyi hazırlayan kişiye geri döner. Bu geri dönüş döngüsü düzeltme yükünü kontrol edene değil veriyi girene bırakır; hatalar zamanla kaynağında azalır.

Mutabakat kontrolleri: toplamlar birbirini tutmalı

Girdi doğru biçimde olabilir ama yine de eksik ya da fazla olabilir. Mutabakat, aynı gerçeğin iki bağımsız kaynaktaki görünümünü karşılaştırır. Bir raporlama çalışma kitabında en az şu kontrolleri kurarım:

• Ayrıntı ile özet: satır bazlı gider sayfasının maliyet merkezi toplamları, yönetim özetindeki rakamlarla birebir eşleşmeli.
• Kaynak ile hedef: muhasebe sisteminden alınan dışa aktarımın toplamı, çalışma kitabına yapıştırılmış hâlinin toplamına eşit olmalı; fark varsa yapıştırma sırasında satır düşmüştür.
• Dönemler arası: bir kalem önceki döneme göre belirlenen eşiğin üzerinde değiştiyse otomatik olarak açıklama ister.
• Tolerans açıkça yazılı: yuvarlama farkları için kabul edilebilir sınır (örneğin 0,01) kodda sabit olarak durur, kişiden kişiye değişmez.

import pandas as pd

TOLERANS = 0.01


def mutabakat(dosya, ayrinti="Giderler", ozet="Ozet", anahtar="maliyet_merkezi"):
    ayr = pd.read_excel(dosya, sheet_name=ayrinti)
    ozt = pd.read_excel(dosya, sheet_name=ozet)

    ayr_toplam = ayr.groupby(anahtar)["tutar"].sum().rename("ayrinti")
    ozt_toplam = ozt.set_index(anahtar)["tutar"].rename("ozet")

    # dış birleştirme: yalnızca bir tarafta olan birim de fark olarak görünür
    karsilastirma = pd.concat([ayr_toplam, ozt_toplam], axis=1).fillna(0.0)
    karsilastirma["fark"] = karsilastirma["ayrinti"] - karsilastirma["ozet"]
    farkli = karsilastirma[karsilastirma["fark"].abs() > TOLERANS]

    genel_fark = ayr["tutar"].sum() - ozt["tutar"].sum()
    return farkli, genel_fark


if __name__ == "__main__":
    farkli, genel = mutabakat("ORNEK-2024-01-giderler.xlsx")
    print(f"Genel fark: {genel:,.2f}")
    if farkli.empty:
        print("Mutabakat tamam: tüm maliyet merkezleri tolerans içinde.")
    else:
        print(farkli.to_string())

Dış birleştirme burada kritik: yalnızca bir tarafta bulunan maliyet merkezi sıfırla doldurulur ve fark olarak görünür. Böylece "özette olmayan yeni birim" ya da "ayrıntıda hiç satırı olmayan birim" de yakalanır. Bu kontrolleri adresli bir istisna paneline dönüştürmenin yollarını Excel kalite kontrol otomasyonu yazısında gösterdim.

Sürüm ve denetim izi

Bir rakam sorgulandığında cevap "hangi dosyanın hangi hâlinden, kimin eliyle" olmalıdır. Bunu sağlamanın yolu paylaşılan klasördeki dosyanın üzerine yazmayı bırakmaktır:

• Kaynak dosyalar değişmez. Sistemden indirilen dışa aktarım kaynak/ altına tarih damgasıyla konur ve bir daha elle açılmaz; her işlem bir kopya üzerinde yapılır.
• Adlandırma kuralı sürümü taşır. rapor_2024-01_v03.xlsx gibi bir ad "son hâli hangisiydi" sorusunu ortadan kaldırır; final, final2, final_son yasaktır.
• Her dosyanın parmak izi alınır. Kontrol betiği çalıştığı dosyanın SHA-256 özetini, zamanı ve bulgu sayısını bir günlüğe yazar; böylece "o gün hangi dosya kontrol edildi" sorusunun cevabı tahmine dayanmaz.
• Değişiklik günlüğü çalışma kitabının içindedir. "Degisiklik" adlı bir sayfa: tarih, kim, hangi sayfa, ne değişti, neden. Formül düzeltmesi de buraya yazılır.

import hashlib
import json
from datetime import datetime, timezone
from pathlib import Path


def iz_kaydet(dosya, bulgu_sayisi, gunluk="denetim-izi.jsonl"):
    ozet = hashlib.sha256(Path(dosya).read_bytes()).hexdigest()
    kayit = {
        "zaman": datetime.now(timezone.utc).isoformat(timespec="seconds"),
        "dosya": Path(dosya).name,
        "sha256": ozet,
        "bulgu": bulgu_sayisi,
        "durum": "GECTI" if bulgu_sayisi == 0 else "RED",
    }
    with open(gunluk, "a", encoding="utf-8") as f:
        f.write(json.dumps(kayit, ensure_ascii=False) + "\n")
    return kayit

Bu kısa günlük, bir denetim sorusuna "şu tarihte şu dosya şu özetle kontrol edildi ve geçti" diye kanıtla cevap vermenizi sağlar. Bir Git deposu varsa kaynak/ klasörü ve günlük dosyası oraya girer; yoksa salt okunur bir klasör ve düzenli yedek de yeterlidir.

El işini azaltan kontrol listesi

Kontroller ne kadar iyi olursa olsun, kimse çalıştırmazsa değersizdir. Bu yüzden dönem kapanışı için tek sayfalık, sırası sabit bir kontrol listesi tutarım ve mümkün olan her maddeyi betiğe devrederim:

• Açılış: kaynak dosyalar kaynak/ altında mı, adları kurala uyuyor mu, özetleri günlükte var mı?
• Girdi: doğrulama betiği sıfır bulguyla geçti mi? Geçmediyse dosya liste sahibine döndü mü?
• Mutabakat: ayrıntı-özet, kaynak-hedef ve dönemler arası kontroller tolerans içinde mi? Eşik dışı kalemler için açıklama yazıldı mı?
• Formül sağlığı: #REF!, #N/A gibi hata değerleri var mı, toplam formülleri son satırı kapsıyor mu, gizli sayfa ya da filtrelenmiş satır kaldı mı?
• Kapanış: sürüm numarası artırıldı mı, değişiklik günlüğü dolduruldu mu, onaylayan kişi ve tarih yazıldı mı?

Bu listeyi çalışma kitabının içinde bir "Kontrol" sayfası olarak da tutabilirsiniz: her madde bir formülle GEÇTİ ya da KALDI üretir, en üstte tek bir hücre tüm sayfanın durumunu gösterir; rapor yalnızca o hücre GEÇTİ derken paylaşılır. Formül sağlığı gibi maddeler openpyxl ile de taranabilir: çalışma kitabı data_only=False ile açılır, hücre değerleri yerine formüllerin kendisi okunur ve toplam aralığının son veri satırını kapsayıp kapsamadığı programla denetlenir. Hangi kontrolün ne kadar değer ürettiğini ölçmek isterseniz, kalite boyutlarını puanlayan ayrı bir skor kartı da kurulabilir.

Nereden başlamalı

Bütün katmanları aynı anda kurmak gerekmez. Sıralama önerim şu:

• Son birkaç dönemde en sık tekrar eden üç hatayı yazın; ilk kurallar bunlar olsun.
• Önce yalnızca raporlayan, hiçbir şeyi düzeltmeyen bir betikle başlayın; güven oluşmadan otomatik düzeltme eklemeyin.
• Her bulguyu satır numarası ve nedeniyle kaydedin; birkaç dönem sonra hangi kuralın işe yaradığını günlük gösterir.
• Kontrol listesini, betik çalıştırılmadan imzalanamaz hâle getirin.

Kalite kontrolün amacı raporu yavaşlatmak değil, aynı hatayı bir daha aramamaktır. Girdi standardı, mutabakat, denetim izi ve kontrol listesi bir kez kurulduktan sonra her dönem aynı şekilde çalışır; el işi düşer, sorulara kanıtla cevap verilir. Bu tür kontrol katmanlarını kurmak veri ve otomasyon danışmanlığı işimin sık yaptığım parçalarından; elinizde her dönem aynı sıkıntıyı üreten bir çalışma kitabı varsa iletişim bölümünden bana yazın, hangi kontrolün önce kurulması gerektiğini birlikte netleştiririz.