Bankavstemming i Excel

Beskrivelse

Bankavstemming i Excel

Jeg har sittet og lett meg grønn etter ettøringer i bankavstemmingen mange ganger. Etter hvert laget jeg et lite verktøy for å avstemme bank med regnskap. I og med at jeg bruker en bankterminal, får jeg én sum i banken som består av flere delsummer i regnskapet. Programmet du kan laste ned her, fikser også den biffen – i hvert fall hvis du ikke har tusenvis av bilag i måneden…

Dette er et program jeg bruker hver eneste måned, og for de som synes det er for gæli å kjøpe et dyrt avstemmingsprogram, er det midt i blinken. Det er testet med eksport fra økonomisystemene Tripletex, Visma Global og Microsoft AX, og med eksport av banktransaksjoner fra Nordea, Eika og flere andre sparebanker. Hvis du får ut transaksjonene dine på Excel eller CSV-format, kan du bruke det. Og det har alle banker og økonomisystemer jeg har vært innom.

Som med flere andre eksempler her på ExcelGuru, var avstemmingsprogrammet laget som et eksempel på bruk av Excel, men så får jeg fler og fler spørsmål om programmet, og har nå gitt det en betydelig ansiktsløftning slik at det blir mer anvendelig uansett hvilken bank og hvilket regnskapsprogram du bruker.

Omtale fra Devold Consulting

Arne Devold i Devold Consulting har fattet interesse for bankavstemmingsprogrammet. Hvis du vil lese vurderingen fra en siviløkonom og autorisert regnskapsfører, kan du klikke her. Det er ganske morsom lesing – i hvert fall for ExcelGuru 🙂

«Så langt alt bra og kjapt utført. Neste vi gjør er å sette avstemmingsregler. Denne undermenyen med avstemmingsreglene er selve «rosinen i pølsen» etter vår mening. Her klarer faktisk Ketil å gjøre det som mange regnskapssystemer strever med å få til i sine løsninger.»

Prinsippet

1. Først sjekker programmet linje for linje om det finner like enkeltsummer med lik dato i bank og regnskap.
2. Så sjekker det om det finnes delsummer i regnskap som blir lik summen i bank. Så sjekkes det motsatt (hvis du ikke har begrenset det i oppsettet), om det finnes delsummer i bank som stemmer med summen i regnskap. Først to delsummer, så tre osv.
3. Etter at denne runden er ferdig, økes tillatt avvik i dato med en, og så er det en ny gjennomgang.

Dette gjentas med det antall delsummer du har satt og med så stort datoavvik som du har satt. Denne fremgangsmåten gjør at avstemmingen har størst mulig sannsynlighet for å matche riktige summer mot hverandre. Og så er det tidkrevende.

Ta en kikk på hvordan det virker:

(Artikkelen fortsetter under videoruten. )

Hvordan jeg bruker bankavstemming

Fra nettbanken laster jeg ned kontobevegelsene for måneden. Dette laster jeg ned på Excelformat. Av bildet under ser du at de eksporterer uten kolonneoverskrifter, men det er likevel rimelig greit å skjønne hva de enkelte kolonnene inneholder.

Etter å ha ført gebyrer og det jeg husker i farten, eksporterer jeg et kontoutdrag for bank i regnskapet. I Tripletex bruker jeg eksport til CSV, siden de lager en formatert Excelrapport som er fin – men ikke passer til denne bruken. CSV-filen fra Tripletex har kolonneoverskrifter og er enkel å finne fram i.

Så starter jeg opp bankavstemmingsprogrammet og klikker på Meny. Når du laster det ned og starter det første gangen, må du svare JA på at du vil aktivere makroer.

Legge inn månedens transaksjoner

Så klikker jeg på «Legg inn banktranser» og kopierer inn dataene fra banken. Klikker på Meny, og så «Legg inn regnskapstranser» og kopierer inn dataene fra Tripletex.

(Når du gjør dette første gangen, må du innom et par av valgene under Oppsett i menyen før du går videre. Se lenger ned)

Når jeg nå klikker på Hent transaksjoner, leses dataene fra bank og regnskap inn i avstemmingsbildet og er klare for behandling. Du kan gjøre dette så mange ganger du har behov for.

Jeg klikker på Bankavstemming, og så har jeg resultatet noen sekunder etterpå.

Statusruten

I statusruten ned til høyre i menyen kan du se hvor mange bilag som er matchet, antall sekunder som har gått og hvor mange tester som er gjort.

Matchede bilag

De bilagene som er matchet mot bank, blir nummerert fra 1 og oppover. Hvis du lukker menyen og klikker på ett av numrene i en av Matchkolonnene, vil dette bli markert med farge, og linjen(e) det er matchet mot vises også i farge som du ser under.

Den blå cella med verdien 24 et matchet mot de fire grønne med verdien 24. Merk at delsummene må ha lik dato for at de skal matches, i dette tilfellet 15.11.2016

Linjer som ikke er matchet

På linjer som ikke er matchet, markeres cella i Matchkolonnen med lys grønn og lys blå farge så du skal se dem tydeligere. På bildet under ser du at det er et avvik på ett øre som gjør at linjene ikke matches.

Slette matchede linjer

Hvis du vil ha bedre oversikt over de linjene som gjenstår, kan du i menyen klikke på «Slett avstemte transer». Da sitter du igjen med de som ikke er avstemt.

Oppsett

Bank og Regnskap

Første gangen du kjører  programmet, må du kjøre oppsettet for at programmet skal hente de riktige feltene fra dataene du har eksportert fra bank og regnskap.

Dette må du gjøre etter at du har kopiert inn data fra bank og regnskap.

Når du klikker på Import Regnskap i Oppsettruten, får du opp bildet under. Feltene i listen til høyre vil variere etter hvilket regnskapssystem du har eksportert dataene fra.

OK-knappen synes ikke før du har vært gjennom og valgt felt for Bilagsdato, Tekst og Beløp.

To beløpskolonner

Hvis du eksporterer data fra f.eks Nordea, har de en kolonne for Inn og en kolonne for Ut. Da huker du av for To beløpskolonner, og feltet beløp II vises. Så velger du Inn som Beløp og Ut som Beløp II.

Ved behov kan du snu fortegnet hvis utgiftskolonnen har negative verdier ved å huke av for «Snu fortegn» til høyre for beløpskolonnen du har valgt.

(Dette viser jeg ikke på videoen siden funksjonaliteten kom etter at videoen ble laget.)

… og hvis dataene ikke har kolonneoverskrifter, noe som er tilfelle med bankeksporten min, ser det slik ut:

Avstemmingsregler

Verdiene du setter her, påvirker hvor lang tid programmet bruker på avstemmingen. Antall delsummer er det som har størst påvirkning.

Maks datodifferanse.

Her setter du hvor mange dager det kan være i differanse på transer fra bank og regnskap. Setter du 31, vil programmet begynne med å finne bilag med lik dato, så med en differanse på en dag, to, og så videre. Derfor vil ikke verdien du setter her påvirke presisjonen, men det kan definitivt påvirke tiden programmet bruker.

Antall beløp en sum kan bestå av.

Jeg har maks 6 innslag på bankterminalen per dag, så jeg setter 6 på «Antall beløp en sum kan bestå av». Hvis du driver butikk og har hundrevis av transaksjoner på en dag, vil det ta svært lang tid…

Det er en forutsetning at alle delbeløp som skal matches mot en enkelt sum må ha lik dato. Delbeløpene må altså ha samme dato, mens det kan være avvik mellom datoene de er ført på i bank og regnskap.

Begrens antall delsummer hvis antall sekunder overskrider…

Hvis du setter antall delsummer høyt og har mange transaksjoner, vil det kunne ta lang tid å gjøre avstemmingen. Hvor lang vet vi ikke før vi har forsøkt. Derfor kan du sette at hvis en beregning tar lenger tid enn f.eks 20 sekunder (eller 120 hvis du er en tålmodig person), vil programmet justere ned testen på antall delsummer til de programmet rekker på det antall sekunder du setter her. På min PC med ca 170 transaksjoner, klarer programmet å teste med 8 delsummer på 20 sekunder. Virker dette uklart, foreslår jeg at du forsøker.

Delsummer forekommer kun i regnskap

Hvis utfordringen din er at du benytter en bankterminal, vil det være én postering i banken som er summen av et antall transaksjoner i banken. Hvis du vet at det motsatte ikke forekommer, kan du huke av her. Dette reduserer beregningstiden betydelig.

Hvor mange minutter orker du å vente?

Programmet vil avslutte etter å ha testet i det antall minutter du setter her – uansett om alle de mulige testene er kjørt. For meg holder det med 1 minutt. Hvis programmet avslutter og det står igjen linjer som skulle vært matchet, kan du forsøke å sette opp antall minutter.

Oppdateringer av bankavstemming:

• 06.12.2016: Oppsett av import, mulighet for å velge to beløpskolonner for systemer som eksporterer Inn/Ut i hver sin kolonne
• 09.02.2017: Redusert tidsbruk ved avstemming av delsummer med ca 60% ved å programmere litt lurere… Mulighet for å snu fortegn på beløpskolonner.
• 22.03.2017: Ytterligere hastighetsforbedring og rettet en potensiell feilkilde.
• 10.04.2019: Korrigert feil i oppsett av bankimport hvis tallfelt er tomt. Takk til Arne Devold
• 16.03.2024: Korrigert feil som kan avstemme likt beløp på lik dato feil

Sist oppdatert 10.04.2019 klokken 10:36