Administratie-spreadsheet online

Het is zover. Naar aanleiding van mijn eerdere blog publiceer ik mijn administratie-spreadsheet. Best wel spannend vind ik dat. Het is een versie met grotendeels fictieve data die ik geanonimiseerd heb. Maar het gaat uiteraard niet om de data. Het gaat om de opzet en om de Visual Basic macro’s die eronder liggen. Mijn spreadsheets zijn gebouwd met Microsoft Excel 2013 en werken bij mij probleemloos onder Windows 10.

Ieder jaar op 1 januari start ik met een nieuwe spreadsheet. Het is een jaaradministratie. Maar omdat ik tegenwoordig nauwelijks meer wijzigingen aanbreng in mijn grootboekrekeningschema kan ik de sheets makkelijk naast elkaar zetten om de trends over meerdere jaren te zien.

Mijn sheet ondersteunt het importeren van de bestanden van twee banken, Rabobank en ABN AMRO. Dat zijn de twee banken waar ik momenteel een rekening heb. Van Rabobank download ik het CSV bestand, van ABN het TXT bestand. De knop hiervoor vind je op het werkblad ‘Jaarrekening’. Deze versie werkt alleen correct met de Rabo macro. Beide banken plaatsen het boekingsbedrag namelijk in een verschillende kolom, en dat moet je dus even aanpassen in de kolom op het werkblad ‘Grootboek’ waar de totalen opgeteld worden.

Het eerste werkblad ‘Jaarrekening’ geeft een samenvatting van de inkomsten en uitgaven per categorie, en vergelijkt ze ook met budget. Ook zie je hier de tussenrekening in de 900 (inkomsten) en de 9000 (uitgaven) groep. Als ik bijvoorbeeld geld overboek van mijn lopende rekening naar mijn spaarrekening dan is dat geen uitgave, maar een interne verschuiving. Idem met geld wat ik naar mijn beleggingsrekening boek. Dat zie je dus (onder de inkomsten en uitgaven) apart weergegeven. Overigens kijkt mijn spreadsheet zelf tot welke datum er boekingen zijn ingevoerd, dat zie je hier in het veld ‘Bijgewerkt tot’.

De boekingen worden tijdens het Importeren op een tijdelijk werkblad ‘Data’ gezet. Kopiëren naar het werkblad Boekingen (waar alle boekingen voor een jaar dus onder elkaar komen te staan) doe ik nog handmatig. Ik ben te bang dat er een keer wat misgaat als ik dat automatisch doe, en dat mijn administratie daardoor in de soep draait. Met de knop ‘Verwijder Datasheet’ gooi ik het tijdelijke werkblad weer weg als de gegevens correct gekopieerd zijn.

De knop ‘Voorcoderen’ activeert een macro die kijkt naar een aantal kenmerken van de boeking. Voorcoderen werkt op de gegevens op het tijdelijke werkblad ‘Data’. De gegevens waarmee de macro vergelijkt staan op hebt werkblad ‘Voorcoderen’. Als de kenmerken overeen komen dan krijgt de boeking op het werkblad ‘Data’ de grootboekrekening toegewezen die op het werkblad ‘Voorcoderen’ bij de boeking staat. Boekingen die de macro niet herkent moet je handmatig voorzien van een grootboekrekeningnummer. Maar op deze manier wordt gemiddeld ruim 80% van mijn uitgaven automatisch aan de juiste grootboekrekening gekoppeld.

Op het werkblad ‘Grootboek’ zie je de uitgaven per grootboekrekening bij elkaar opgeteld. Hier kun je per rekening ook een budget ingeven. Op basis daarvan (en van de datum tot waar de boekingen zijn bijgewerkt) berekent de spreadsheet hoeveel budget er YTD (Year To Date – tot op dit punt in het jaar) beschikbaar was. Ik werk alleen met jaarbudgetten, niet met maandbudgetten.

Op het werkblad ‘Creditcard’ houd ik gedurende de maand mijn creditcard-uitgaven bij. Zodra de creditcard wordt afgeschreven signaleert mijn spreadsheet dat tijdens het Voorcoderen (melding ‘Er is een credit-card boeking’). Als ik die krijg dan druk ik op de knop ‘Creditcard Verwerken’ op het werkblad ‘Jaarrekening’. De macro zoekt dan de juiste creditcarduitgaven erbij en zet die onder de boekingen op het werkblad ‘Data’. Hij doet dat van boven naar beneden vanaf het werkblad ‘Creditcard’ en stopt zodra het juiste totaalbedrag van de creditcardboeking bereikt is. De creditcardboeking zelf krijgt als grootboekrekeningnummer nul (‘0’), zodat die ook niet meetelt in de uitgaven. Het zijn immers de afzonderlijke betalingen die belangrijk zijn voor het totaalbeeld.

Tenslotte vind je op het werkblad ‘Jaarrekening’ nog de knop ‘Kashbook importeren’. Voor het bijhouden van mijn uitgaven in contant geld (dat zijn er steeds minder) gebruik ik de app ‘Kashbook’ op mijn smartphone. Vanuit Kashbook kun je alle boekingen in CSV format aan jezelf sturen. Die importeer ik en ze worden dan verwerkt op de juiste grootboekrekening, ook weer op een tijdelijk werkblad ‘Data’. Vandaar kun je ze ook weer zelf kopiëren naar het werkblad ‘Boekingen’.

Alle te importeren bestanden lees ik in vanuit mijn ‘Downloads’ directory, die je in kunt stellen op het tabblad ‘Handleiding’. Ook kun je hier instellen in welke directory de importbestanden na verwerking opgeslagen moeten worden, en ook moet je hier de volledige naam van de administratie opgeven. Dat wordt gebruikt in onder andere de import-macro’s.

Met het contant geld, de creditcard-uitgaven en alle boekingen en pinbetalingen vanaf mijn bankrekening heb ik een compleet beeld van al mijn inkomsten en uitgaven.

Hier ga je naar de downloadpagina.

Over een tijdje publiceer ik ook mijn beleggingsadministratie. Die is ietsje ingewikkelder dan deze. Ik hoop vooral dat andere mensen ook hun spreadsheets en macro’s online gaan zetten. Want ik wil graag ook leren van anderen!

Wat vind je van mijn spreadsheet?

Tussendoor-spreadsheet-klooi-blogje

Het was weer eens zover. Eens in de zoveel tijd erger ik me aan mijn beleggingsspreadsheet. Dan duren de verwerkingen van mijn weekrapportjes weer te lang, of doet ‘ie er weer te lang naar mijn zin over om mijn rapportages te laten zien op het scherm. En je kunt wel ieder half jaar een nieuwe laptop met snellere processor en nog meer geheugen kopen, maar dat lost het vaak ook niet op.

Dus eens in de zoveel tijd ga ik weer op zoek naar manieren om mijn macro-code te optimaliseren. Zo ook vandaag.

Allereerst ben ik ervoor gaan zorgen dat de belangrijkste macro’s ongestoord kunnen draaien. Dat zijn de macro’s voor het Importeren en Verwerken van de wekelijkse koersrapporten, voor het bijwerken van mijn Rapportages (voor de liefhebbers: Pivot-tabel) en voor het bijwerken van de Grafieken. Mijn spreadsheet zit vol met functies die zichzelf bijwerken als er iets verandert. En dat vertraagt natuurlijk enorm als ze dat ook doen tijdens de bovengenoemde verwerkingen. Goede tips vond ik hier en met name ook hier.  Die laatste oplossing heb ik vrijwel één-op-één overgenomen.

Aan het begin van mijn code heb ik dus drie variabelen toegevoegd die voor mijn hele spreadsheet gelden.

Public CalcState As Long 
Public EventState As Boolean 
Public PageBreakState As Boolean

Verder heb ik de twee subroutines toegevoegd:

Sub OptimaliseerCode_Begin()

Application.ScreenUpdating = False
EventState = Application.EnableEvents
Application.EnableEvents = False
CalcState = Application.Calculation
Application.Calculation = xlCalculationManual
PageBreakState = ActiveSheet.DisplayPageBreaks
ActiveSheet.DisplayPageBreaks = False

End Sub

En aan het eind moet je dat natuurlijk weer ongedaan maken.

Sub OptimaliseerCode_End() 

ActiveSheet.DisplayPageBreaks = PageBreakState 
Application.Calculation = CalcState 
Application.EnableEvents = EventState 
Application.ScreenUpdating = True 

End Sub

Vervolgens moet ik die code natuurlijk aanroepen in mijn eigen macro’s. Dat doe ik op de volgende manier in elk macro en elke functie die van toepassing is:

'Optimaliseer Code 
Call OptimaliseerCode_Begin 

'Hier staat dan mijn eigen code... 

'Optimaliseer Code 
Call OptimaliseerCode_End

En kijk mij eens een mooie plugin hebben om die code professioneel te publiceren! In de eerste test werden de macro’s voor importeren en verwerken ongeveer vier keer zo snel door deze ingreep. En ook bij het actualiseren van mijn weekrapportage en mijn grafieken was er een behoorlijke sneheidswinst. De nerd in mij is weer dolgelukkig.

Ik ga nog even apart kijken naar de code voor de Pivot. En voor de import overweeg ik om te schakelen van ‘veld voor veld inlezen’ naar ‘in één keer inlezen in een array, en in één keer wegschrijven in een sheet’. Ik lees op diverse plekken dat dat ook veel snelheidswinst op kan leveren. Maar dat is een wat ingrijpender operatie, daar kom ik nog wel op terug.

Ben ik weer te nerdy?

Spreadsheets delen

Geldnerd is dol op spreadsheets. Dat is vrij algemeen bekend. En vooral mijn financiële administratie en beleggingsspreadsheet zitten vol met macro’s die het leven makkelijker maken en het voeren van de administratie terugbrengen tot (bijna) één druk op de knop. Daar ben ik best wel een beetje trots op.

Regelmatig krijg ik de vraag om meer details te geven, of om de spreadsheets te delen. Dat wil ik best doen. Maar ik worstel nog een beetje met de manier waarop. Uiteraard zou ik mijn persoonlijke gegevens verwijderen, maar dat maakt mijn worsteling niet kleiner. Want de spreadsheets zijn helemaal op mijn persoonlijke situatie (en de banken en beleggingswebsites waarmee ik werk) geschreven. En daarnaast moet je behoorlijk wat weten van Excel en Visual Basic om er echt mee aan de slag te kunnen. Kun of wil je dat niet, dan kun je beter met een kant en klaar pakket aan de slag.

Ik kan natuurlijk ook een serie blogjes schrijven, en in ieder bericht een functie toelichten. Hoe is de logica opgebouwd en hoe heb ik het geprogrammeerd, inclusief de Visual Basic code. Maar ja, aan één functie heb je ook weinig. Het is de combinatie en het samenspel die het interessant maakt. En volgens mij zitten veel lezers niet te wachten op nog meer ‘nerdy’ berichtjes met programmacode en technische verhalen. Of toch wel?

Kortom, ik ben er nog niet uit. Dus dacht ik: laat ik het gewoon eens aan jullie voorleggen. Wat lijkt jullie een goede manier om dit te delen? Of kan ik dat maar beter niet doen omdat er dan massaal afgehaakt wordt? Of hebben jullie sowieso geen behoefte aan spreadsheets?

Afblijven! En meer capaciteit

Waarschuwing vooraf: dit is een blog in categorie ‘de nerd in mij’….

201610-binary-codeSoms knutsel ik teveel aan mijn spreadsheets. En dan ontstaan er wel eens onbedoelde neveneffecten en/of onverwachte fouten. Die dan ook weer knutselarij kosten om op te lossen. En soms verzucht ik dan: ‘Geldnerd, blijf er eens vanaf… Het werkt toch…’.

Zo ook onlangs. Ik had een nieuwe functie gebouwd die per grootboekrekening per maand uitrekent wat ik er aan uitgegeven heb. Niet omdat het moet, maar omdat het kan… En het is handig voor sturing, ontdekken van seizoenspatronen, en het uitrekenen van het spaarpercentage per kwartaal (per maand doe ik niet, dat varieert teveel en daar word ik onrustig van).

Nu is het in Excel zo dat User Defined Functions (UDFs – door een gebruiker zelf geprogrammeerde functies) niet automatisch herberekenen. Tenzij je ze ‘volatile’ maakt. En dat had ik dus gedaan.

En daar kwam het neveneffect. Want de door mij geprogrammeerde functie ‘MaandBedrag’ komt (150 grootboekrekeningen maal 12 maanden) 1.800 keer voor in de spreadsheet. En die werden dus allemaal herberekend als ergens in de spreadsheet iets verandert. Dus de import van mijn bankboekingen, die normaal een paar seconden duurt, had ineens ruim 10 minuten nodig.

Dat was niet de bedoeling. Dus heb ik ‘Volatile’ maar weer uitgezet. En een andere list bedacht. Ik heb een ‘dummy-veld’ als parameter toegevoegd aan de ‘Maandbedrag’ functie. Want als een parameter wijzigt, wordt de functie wel automatisch herberekend. Als ik een herberekening wil (en dat is eigenlijk alleen aan het eind van een import van boekingen) dan hoog ik de waarde van de dummy op met 1. Dat triggert de ‘Maandbedrag’ functie en die wordt dan keurig bijgewerkt. Kost een paar seconden, maar niet 10 minuten.

Ik heb ook een wijziging doorgevoerd in het starten van Excel. Daar heb ik de parameter ‘/HIGH’ aan toegevoegd. Die zorgt ervoor dat Excel standaard meer processorcapaciteit en geheugen krijgt toegewezen. Want ik merkte dat Excel, zelfs tijdens zware berekeningen, maximaal 10 – 15 procent van de processorcapaciteit gebruikte. Nu is dat meer, en dat scheelt ook weer tijd.

Heb jij ook wel eens last van onbedoelde neveneffecten in je spreadsheets?

‘Klooien’ met spreadsheets

Afgelopen weekend heb ik weer ouderwets een paar uur zitten ‘klooien’ met mijn spreadsheets. Ik heb er eerder over geschreven en kreeg destijds gelukkig ook veel reacties van medestanders. Fijn gevoel om de sheets weer wat slimmer te maken.

De aanleiding was praktisch. Geldnerd had al een slimme persoonlijke administratie, die automatisch de gedownloade boekingen van de Rabobank verwerkte. Iets soortgelijks had ik ook voor onze bankrekeningen in het Verre Warme Land. Maar de gezamenlijke bankrekening van Geldnerd en Vriendin loopt bij de ABN AMRO en daarvoor had ik de automatische import macro nog niet aangepast. Dat was wel nodig, want nu Geldnerd en Vriendin weer samen in Nederland wonen is dit de meestgebruikte rekening geworden. Ik heb alles nog een slagje makkelijker gemaakt, de macro verplaatst ook automatisch het bestand van de download-directory naar mijn archief. Ik hoef dus helemaal niks meer handmatig te doen.

Ook heb ik de spreadsheet nu zo ingericht dat ik de uitgaven nu per grootboekrekening  per maand zie. Ik wat beter kijken naar seizoensinvloeden, en kijken of ik zinvol van een jaarvergoeding naar een maandbetaling kan overstappen.

Binnenkort komt er nog een klusje aan. De integratie van mijn kasboek in de administratie-spreadsheet. Maar dat heeft nu even geen haast.

Heb jij onlangs nog fijn zitten sleutelen aan een spreadsheet? En wat heb je gebouwd?

De beleggingsspreadsheet (3) – Analyses

  • Berichtcategorie:Beleggen

Een tijdje geleden schreef ik over mijn verherbouwde beleggingsspreadsheet, en over de statistieken die ik voor mijn portefeuille en fondsen bereken. In de sheet heb ik ook een aantal analyses ingebouwd. Die analyses zie je vooral terug in de kleur van een aantal velden. Dat geeft mij een idee van de ‘stemming’ in mijn portefeuille en geeft mij ook verkoopsignalen.

201511BeleggenPivot3-Blur

Zo vergelijk ik de huidige koers van mijn fondsen automatisch met het 25-daags gemiddelde, het 50-daags gemiddelde en het 200-daags gemiddelde. Als de huidige koers boven respectievelijk beneden die gemiddeldes ligt dan zegt dat volgens mij iets over ‘de trend’. Is het fonds ‘happy’ en zit het in een positieve ‘flow’, zullen we maar zeggen. Vooral als een fonds beneden het 200-daags gemiddelde zakt moet ik op gaan passen.

Ook kijk ik naar de Stop/Loss (S/L). Dat zegt mij hoever een aandeel onder z’n hoogste koers zit (sinds het moment dat ik gekocht heb). Is de S/L nul, dan is de huidige koers de hoogste, hoera! Maar als de S/L onder de -10% zakt moet ik op gaan passen. Dan is ‘de trend’ voor dit fonds misschien wel omgekeerd, van een structureel stijgende lijn naar een dalende lijn. Zeker een combinatie met het 200-daags gemiddelde is voor mij een signaal om verkoop te overwegen.

Veel grote beleggers gebruiken dit soort analyses om ‘het systeem’ automatisch aankoop- en verkooporders uit te laten voeren op de beurs. Zover ga ik niet. Kopen of verkopen blijft altijd mijn eigen beslissing. Ik houd ook altijd goed bij wat de beurzen als geheel doen, wat de stemming van ‘de markt’ is. Maar mijn ingebouwde analyses helpen me wel om te kijken welke fondsen in mijn portefeuille wat extra aandacht nodig hebben. Dat is nuttig, zeker toen mijn portefeuille wat groter werd en meer verschillende fondsen bevatte.

Welke analyses gebruik jij om je beleggingen te volgen?