Gestiamo l'estratto conto bancario con Excel

Impariamo come usare Excel non solo per controllare estratto conto, riassunto scalare, interessi, commissioni e spese, ma anche il raggruppamento per causale.

Quello che vi proponiamo è un tutorial completo per imparare a gestirsi da soli e in maniera definitiva il vostro estratto conto bancario con Excel, tenendo sotto controllo tutte le operazioni di un dato periodo, tipicamente l'anno solare.

Consigliamo magari ai meno pratici di dare un'occhiata preliminare anche alla nostra miniguida: Come si legge l'estratto conto .

Ci sono almeno 3 vantaggi nel giungere a questo risultato:

1. Avrete la possibilità di tenere sotto controllo il modo in cui si comporta effettivamente la vostra banca

2. Potrete in ogni momento avere il riepilogo delle causali di addebito e di accredito per qualsiasi intervallo date.

3. Il terzo vantaggio è che una volta che avrete il vostro progetto di foglio di calcolo ben funzionante e tarato, potrete compiere tutte le simulazioni che vorrete, per esempio modifica delle condizioni future, dei flussi di cassa o di entrambe.

Ma cominciamo senz'altro da un esempio concreto. Partiamo come al solito con foglio di Excel vuoto. La prima operazione da compiere sarà quella di importare le righe del vostro estratto conto, sia esso cartaceo o già in formato elettronico, nel primo foglio di calcolo della cartella che chiamaremo 'Estratto Conto'.

Se i dati sono in forma cartacea, potreste sempre scansionare l'estratto conto con uno scanner da tavolo e, tramite la funzionalità OCR (Optical Character Recognizing o Riconoscimento Ottico dei Caratteri) del vostro software di acquisizione, convertire l'immagine del documento in righe di testo su un file di testo (.txt o .asc) e poi utilizzare l'importazione guidata file di testo di Excel, oppure copiare e incollare.

Se disponente già di un file in formato elettronico (se magari avete già un conto corrente online) le cose sono più semplici, ma occorre comunque sbrigarsela con formattazioni varie e problemi di allineamento delle colonne.

Nella figura 1 vedete un esempio di file proveniente da scanner, si tratta di un documento TIFF con il testo già convertito da OCR.

Abbiamo utilizzato allo scopo un accessorio gratuito di Microsoft Office 2003 che si chiama Microsoft Office Document Scanning.

Se non lo vedete nel gruppo dei programmi Office, provate a reinserire il CD o DVD originale e a reinstallarlo tramite la procedura guidata Installazione/Rimozione Componenti Aggiuntivi di Office.

Una volta che il testo è stato riconosciuto potete utilizzare direttamente le funzioni di copia (CTRL + C) e Incolla (CTRL + V) presenti in tutti le applicazioni Windows per trasferire il testo direttamente nel foglio di Excel.

Un consiglio importantissimo è quello di procedere per colonna anzichè per riga, e poi aggiustare a mano eventuali discordanze: noi abbiamo fatto in questo modo ed abbiamo risparmiato un mucchio di tempo!

In particolare, come spesso capita, la colonna a dare problemi è proprio quella delle causali, di solito molto lunghe e quindi spostate su più righe.

Per ovviare anche a questo inconveniente abbiamo dapprima copiato e incollato in Excel tutte le altre colonne ed eliminato le righe vuote (basta ordinare l'intervallo celle in ordine alfabetico,quindi eliminare le righe vuote e infine riordinare per data contabile).

Per ultima abbiamo copiato la colonna delle causali così com'era ma sul blocco note di Windows, anzichè su Excel, in un primo tempo.

Disattivando l'opzione 'A capo automatico' nel menù Formato del Blocco Note ci siamo assicurati che le causali restassero ciascuna su una riga differente e a questo punto abbiamo copiato e incollato la zona come colonna sotto 'DESCRIZIONE' ( figura 2 )

Anche nel caso disponiate già di un file di testo, scaricato magari dal vostro servizio internet di home banking, vi consigliamo di preparare il file con la stessa tecnica, prima di importarlo in Excel.

In particolare occorre assicurarsi che le colonne siano di larghezza fissa e che non ci siano righe vuote o non allineate, a meno che il file non sia in formato delimitato da carattere di doppie virgolette o tabulazione o altro.

Una volta compiute queste operazioni preliminari dovreste poter giungere abbastanza rapidamente ad un risultato come quello della figura 3 .

La prima riga, contenente le etichette di colonna: Data Contabile, Data Valuta, Addebito, Accredito e Descrizione, le abbiamo inserite manualmente e poi bloccato la prima riga dallo scorrimento verticale (menù Finestra, Blocca riquadri, quando la cella selezionata è sulla seconda riga).

Una volta riportate in Excel tutte le righe che ci interessano e relative al periodo corrispondente almeno ad una liquidazione competenze, trimestrale, semestrale o annuale, il grosso del lavoro è già fatto.

Non preoccupatevi se vi sono delle righe che iniziano con SALDO INIZIALE o SALDO FINALE, ora vedremo subito come costruirci una funzione che somma soltanto gli addebiti e gli accrediti effettivi, saltando i saldi automaticamente.

Sia per ottenere il saldo addebiti e il saldo accrediti di periodo o totali, sia per riepilogare gli importi per causale, ci occorrono un paio di funzioni personalizzate, basata sulla funzione SOMMA.SE di Excel ma più flessibili, perchè consentono di lavorare con più precisione sul parametro Criterio.

Il parametro Criterio di SOMMA.SE è facile da impiegare quando si lavora con valori numerici ma molto più complesso se si lavora con stringhe, cioè con dati di testo di una certa lunghezza, come è nel nostro caso.

La sintassi generale di SOMMA.SE(Intervallo;Criterio;int_Somma) dove 'Intervallo' è il range di celle sulle quali opera il Criterio, il 'Criterio' è la condizione di filtro e 'Int_Somma' è il range di celle che contiene i valori che si intendono sommare condizionatamente.

Nel progetto di Excel utilizziamo il Criterio sia con riferimento stringa assoluto come nel caso di cella C204:

=SOMMA.SE(E2:E201;"*SALDO*";C2:C201)

in questo caso ci serve sommare il valore di ADDEBITO solo in corrispondenza delle righe descrizione causale ove è presente la stringa "SALDO" in qualunque posizione.

Utilizziamo anche la funzione SOMMA.SE con riferimento relativo di cella come nel caso della cella C212

=SOMMA.SE($E$2:$E$201;"*"&E212&"*";$C$2:$C$201)

in questo caso ci serve sommare il valore di ADDEBITO solo in corrispondenza delle righe descrizione causale nelle quali è presente il valore stringa contenuto in E212, cioè PRELEVAMENTO POSTAMAT

Il codice VBA della macro va incollato nel Modulo1 della cartella corrente. Occorre quindi prima aggiungere il Modulo1 al progetto e quindi salvarlo con estensione .xlsm se avete Excel 2007-2010 oppure .xls se avete Excel 2000-2003.

Nella finestra di dialogo che viene aperta facendo click sulla sezione Formule della barra multifunzione, quindi sull'icona Inserisci funzione (facciamo qui riferimento a Excel 2010), basta scorrere l'elenco a discesa di sinistra (Categorie) fino a trovare la voce 'Matematiche e trigonometriche': nel riquadro di destra sarà possibile selezionare direttamente la funzione SOMMA.SE

Ora possiamo procedere al controllo e al bilanciamento dei totali dare (addebito) e avere (accredito).

Ricordiamo che se il saldo di apertura (o chiusura) conto ad una certa data è negativo allora il relativo importo verrà esposto nella colonna ADDEBITO, viceversa se il saldo di apertura (o chiusura) è positivo allora il relativo importo verra esposto nella colonna ACCREDITO.

Nella cella C203 ( figura 4 ) otteniamo quindi il TOTALE della colonna ADDEBITO per tutte le righe esclusi i SALDI, mediante la nostra funzione SOMMA_SENONCONTIENE, come già visto.

Nella cella D203 otteniamo il TOTALE della colonna ACCREDITO per tutte le righe esclusi i SALDI, mediante la nostra funzione SOMMA_SENONCONTIENE.

La differenza tra i 2 TOTALI è riportata in D207 ed è pari a € 950,69 ( figura 5 )

Sottraendo da questo importo il SALDO a INIZIA ANNO, che era NEGATIVO per € 19,25 otteniamo esattamente il SALDO FINALE AVERE di € 931,44.

Questa è una prima conferma che le funzioni personalizzate hanno funzionato.

Ma siccome non ci accontentiamo, per ottenere un ulteriore conferma, abbiamo inserito in C204 e D204 le funzioni personalizzate SOMMA_SECONTIENE, con parametro criterio ="SALDO" e infine in C205 e D205 le SOMME semplici degli stessi intervalli.

Si può verificare facilmente che C203+C204=C205 e che D203+D204=D205.

Il passo successivo consiste nel riportare nel range (intervallo) E212:E223 tutte le CAUSALI che compaiono almeno 1 volta nel nostro estratto conto.

Da notare che la causale è in forma abbreviata e non è necessario che corrisponda esattamente all'inizio della riga, potrebbe iniziare da un qualunque carattere intermedio.

Quello che vogliamo ottenere è il RAGGRUPPAMENTO degli importi per CAUSALE e riverificare il loro totale generale con i totali di rigo 203 ancora una volta.

Inseriamo nella cella C212 la formula:

SOMMA_SECONTIENE(E2:E201;E212;C2:C201)

Questa volta, al posto di una costante stringa di testo ("SALDO") abbiamo inserito in corrispondenza del parametro Criterio, una variabile, più precisamente un riferimento di cella (E212) che rimanda appunto alla stringa "PRELIEVO POSTAMAT".

La somma di € 7.068,61 rappresenta quindi la somma condizionata di tutte le righe dell'estratto conto per 'PRELIEVO POSTAMAT' ( figura 6 )

Ripetendo lo stesso procedimento per tutte le altre causali di addebito in C212:C220 e per le causali di accredito in D222:D223, otteniamo il RIEPILOGO GENERALE per CAUSALE.

Se sommiamo ora le 2 colonne, otteniamo gli stessi risultati di rigo 203.

Se i totali non vi dovessero quadrare, quasi certamente il motivo è dovuto al fatto che qualche riga contiene dei caratteri errati, magari dovuti al riconoscimento ottico dei caratteri OCR oppure ad un errore di digitazione o all'inserimento di spazi, ecc.

Per controllare le righe, basta mettere in ordine alfabetico crescente per 'DESCRIZIONE' tutto il foglio e salteranno subito all'occhio le divergenze.

Infine, se volete ottenere il riepilogo per intervalli di date differenti, basterà modifica i parametri relativi agli intervalli nelle funzioni SOMMA_SECONTIENE (primo e terzo parametro) dato che le righe sono già ordinate per DATA CONTABILE ( figura 7 )

Inseriamo ora un secondo foglio, che chiameremo 'Raggruppamenti per Valuta'.

Questo foglio non corrisponde ancora al Conto Scalare, ma è un "passo intermedio" per meglio comprendere come si arriva ai numeri creditori e debitori e per rendere più semplice anche il codice VBA che d'ora in avanti genererà tutto automaticamente ( figura 8 ).

Ci soffermiamo soltanto sulle istruzioni chiave delle routine, il cui codice completo potrete scaricare in fondo al tutorial.

Intanto occorre preparare a mano la prima riga, che conterrà le etichette 'Data Valuta', 'Addebito', 'Accredito', 'Differenza' e 'Saldo' (notare che non è più presente la colonna 'Data Contabile').

Ci posizioniamo quindi sulla seconda riga e blocchiamo i riquadri al solito modo.

La Macro da richiamare si chiama 'AggiornaSaldiPerValuta' e sarà richiamabile dal Menù 'Strumenti', sottomenù 'Macro', voce 'Macro' una volta che avrete incollato in un vostro modulo il codice relativo, la macro sarà visibile nell'elenco: fate clic sul pulsante 'Esegui' per eseguirla.

Attenzione! Prima di eseguire la Macro occorre che il foglio attivo sia 'Estratto Conto'.

Cosa fa in sostanza la Macro? Sfrutta la funzionalità 'SubTotali' di Excel, dopo avere ordinato il range (2:201) per data valuta, quindi ricopia soltanto i totali per valuta e li riporta nel foglio 'Raggruppamenti per Valuta'.

Al termine del ciclo viene selezionato il foglio 'Raggruppamenti per Valuta'.

L'ultimo saldo dovrà corrispondere esattamente al saldo finale dell'Estratto Conto.

Nella versione definitiva abbiamo riunificato le 2 macro in una unica, quindi in fondo alla macro 'AggiornaSaldiPerValuta' viene richiamata la seconda macro, 'AggiornaContoScalare'.
Occorre inserire manualmente un altro foglio, che chiameremo 'Riassunto Scalare', quindi le etichette in prima riga 'VALUTA', 'SALDI PER VALUTA', 'GIORNI', 'NUMERI DEBITORI' e 'NUMERI CREDITORI'

La Macro 'AggiornaContoScalare' invece ha l'importantissimo compito di calcolare i GIORNI intercorrenti tra una DATA VALUTA e la SUCCESSIVA (elaborando le righe di 'Raggruppamenti per Valuta' e non più di 'Estratto Conto').

A questo punto è facile calcolare i NUMERI DEBITORI e i NUMERI CREDITORI, allo stesso modo in cui procede la vostra banca.

In particolare i NUMERI DEBITORI sono pari al valore assoluto del Saldo per Valuta Negativo moltiplicato i GIORNI, mentre i NUMERI CREDITORI sono pari al valore assoluto del Saldo per Valuta Positivo moltiplicato per i GIORNI.

La funzione utilizzata per calcolare i GIORNI è la funzione VBA DateDiff('Intervallo', 'DataIniziale','DataFinale') con 'd' (day) come valore per Intervallo.

Al termine il foglio 'Riassunto Scalare' dovrebbe assomigliare a quello della figura 9 .

I NUMERI DEBITORI (in rosso) e quelli CREDITORI (in blu) vengono quindi riportati nell'ultimo foglio, 'Conteggio Competenze' che ricalca pari pari l'omonimo documento che periodicamente deve consegnarvi la banca insieme al riassunto (o conto) scalare e all'estratto conto.

Il Conteggio Competenze è composto solitamente di 4 sezioni, la prima (1. INTERESSI CREDITORI) riepiloga i numeri creditori per date di decorrenza del tasso attivo differenti ( figura 10 ).

La formula che consente di calcolare gli Interessi Creditori è : (Numeri Creditori X Tasso : giorni)

Nel nostro caso, non essendo mai variate le condizioni durante l'anno, abbiamo: (1.213.061,89 x 1,25% : 365) = € 41,54

A questo importo, il quale rappresenta i vostri interessi attivi lordi, deve essere applicata per legge la ritenuta del 20%, quindi nel nostro caso la banca si trattiene € 8,31 (€ 41,54 x 20%), somma che verserà all'erario.

Gli Interessi Creditori NETTI ammontano quindi a € 33,23 (€ 41,54 - € 8,31).

Il procedimento per quanto riguarda gli INTERESSE DEBITORI è analogo. Nel nostro caso non è stato applicato alcun interesse debitore.

Fate attenzione alle condizioni di tasso che la banca vi ha applicato alle varie decorrenze, avete comunque 60 giorni di tempo per contestare.

La sezione 3, relativa alle spese e quella finale, relativa al RIEPILOGO FINALE delle COMPETENZE, non presenta particolari problemi di interpretazione.

Per scaricare il codice VBA relativo a questo tutorial sequite le istruzioni contenute nel riquadro seguente:

Avvertenza !

Il file dell'esempio Estratto Conto Bancario con Excel contenente il codice VBA completo disponibile nell'area 'Tutorial Download' riservata agli iscritti alle nostre newsletters.

Si tratta di un file di Excel completo di macro e funzionante.

Iscriviti ora alla newsletter 'Comprendere il PC',  gratis! Puoi utilizzare il riquadro qui a lato sulla sinistra della pagina in alto oppure seguire direttamente questo collegamento:

http://www.mc2elearning.com/html/comprendere_il_pc.html

(il link per il download sulla mail di conferma iscrizione, dopo la convalida dell'indirizzo e-mail con il quale vi siete iscritti alla newsletter)





I Tutorial di Microsoft Excel


Home page MC2 E-Learning



Dr.Maurizio Cucchiara

Amministratore unico
MC2 E-Learning S.a r.l.
Formazione per il Web
autore del corso:
"Excel Corso Base"
disponibile on line su questo sito
http://www.mc2.it/html/excel.html

Condividi sui social network:

Social buttons



Iscriviti al nostro
nuovissimo
canale Youtube!


Iscrivi al nostro canale Youtube



Potrai restare aggiornato
sui nostri pi recenti
video-tutorial su:
Microsoft Access ed Excel
e molto altro...



Cerca sul web con Google:




Guarda il video-tutorial
sul nostro canale Youtube:


Calcolo Estratto Conto Bancario con Excel

NOVITA'!

'VIDEO TUTORIAL
DOWNLOAD AREA'

(Fai clic sull'immagine
qui sotto per vedere
una anteprima della
pagina di accesso)



L'accesso riservato agli iscritti ai corsi on line www.mc2.it

Acquista anche uno solo
dei nostri corsi
per avere immediatamente
accesso alla
'Videotutorial Download Area': facile, basta un clic:

http://www.mc2.it



Iscriviti alla newsletter: E' GRATIS! (?)

 Nome: (?)

 (?)

e-mail: (?)

 (?)

Newsletter:

formato preferito:

Microsoft Access (?)

Impariamo Excel e il VBA(?)

Internet "da zero"! (?)

Html (?)

Consiglio

(?) = Aiuto

Leggi qui la Informativa completa sulla privacy.