Geldnerd.nl

Bloggen over persoonlijke financiën

Tag: Excel (page 1 of 4)

Administratie met KNAB-import

Een paar weken geleden publiceerde ik mijn vernieuwde administratiespreadsheet, met een aantal verbeteringen ten opzichte van de eerdere versie. Daarbij beloofde ik al dat er snel nog een nieuwe versie met extra functionaliteit zou volgen. En hier is ‘ie.

Deze nieuwe versie biedt, naast ondersteuning voor Rabobank en ABN AMRO, ook ondersteuning voor het importeren van CSV-bestanden van KNAB. Met dank aan lezer Jeroen, die mij een voorbeeldbestand stuurde.

Daarnaast werkt nu de controle of transacties wel goed op elkaar aansluiten. Mijn ervaring is dat de opeenvolgende bestanden van banken niet altijd goed op elkaar aansluiten, ik miste wel eens een aantal boekingen als je wekelijks een bankbestand importeert. In deze versie krijg je daar automatisch een melding van. Het heeft me wel wat tijd gekost voordat ik mijn eigen stomme programmeerfout in deze functie gevonden had…. Maar nu doet ‘ie het.

Ik blijf het herhalen: mijn spreadsheets zijn vooral ter inspiratie, om te laten zien wat er allemaal kan. Voor mij werkt dit naar volle tevredenheid. Ik ga nu weer aan de slag met mijn nieuwe Vermogensdashboard. Ook die zal ik in Excel bouwen, ik wil het allemaal zo eenduidig mogelijk houden.

De nieuwe versie van de administratiespreadsheet is beschikbaar op mijn Downloads-pagina.

Hoe is het met jouw spreadsheets?

Integraal Vermogensdashboard

Met mijn spreadsheets houd ik mijn persoonlijke financiën goed in de gaten. De financiële administratie, mijn beleggingsadministratie, en de stand van het huis. Jaarlijks maak ik mijn balans op. De afgelopen jaren heb ik mijn spreadsheets ook behoorlijk uitgebreid. Vooral met rapportages. Hoe staat het huis ervoor? Rapportages en grafieken over mijn beleggingen. Die ik onder andere gebruik in mijn kwartaalrapportages.

Maar één ding ontbreekt nog. Een integraal dashboard. Waar ik in één oogopslag kan zien hoe ik ervoor sta. Want daarvoor moet ik nu nog gegevens uit meerdere bronnen combineren. En daardoor mis ik soms overzicht.

Een voorbeeld: ik wil naast de omvang ook graag de opbouw van mijn vermogen in de gaten houden. Wat zit er in ons huis, wat is er in cash, en wat zit er in de beleggingen? En die laatste categorie dan liefst ook nog uitgesplitst in aandelen en obligaties. En dat dan ook uitgezet in de tijd, zowel in bedragen als in percentages. Op die manier houd ik zicht op mijn risicoprofiel. Maar daarvoor moet ik nu gegevens uit mijn financiële administratie en beleggingsspreadsheet handmatig combineren. Dat past uiteraard niet bij mijn ‘alles geautomatiseerd’ principe…

De afgelopen periode heb ik nagedacht wat ik zo allemaal op mijn Integrale Vermogensdashboard zou willen zien. Naast het bovenstaande, in elk geval ook:

  • Een overzicht van mijn vermogensopbouw per valuta. Naast Euro’s heb ik immers ook beleggingen in Amerikaanse dollars. En in het verleden heb ik ook beleggingen in andere valuta gehad. Het is goed om een beetje in de gaten te houden hoe de valuta-verhoudingen in mijn vermogen liggen.
  • Van ons huis wil ik de huidige waarde zien waar ik mee reken, nu nog de aankoopwaarde, maar binnenkort de WOZ-waarde. En ik wil het nog af te lossen deel van de hypotheek zien. En natuurlijk de stand van mijn grote en kleine buffer. De grote buffer bevat genoeg geld om 6 maanden normaal te kunnen leven, en de kleine buffer is bedoeld voor grotere, geplande uitgaven waar ik gedurende het jaar voor spaar.

Verder wil ik graag een aantal belangrijke specifieke indicatoren zien, bijvoorbeeld:

  • Het ontvangen dividend
  • Return on Investment
  • XIRR
  • Spaarpercentage

Tsja, en als mijn gedachten dan eenmaal hun gang gaan, dan wil ik ook echt een compleet beeld. Dus wil ik ook de stand van mijn pensioen zien, en de AOW. Met daarnaast de stand van mijn vermogen, en mijn gemiddelde maandelijkse uitgaven, kan ik in één oogopslag zien hoe ik ervoor sta in mijn reis naar onafhankelijkheid.

Deels zijn het dingen die ik over een periode bekijk: het laatste jaar, het laatste kwartaal, of YTD (Year-To-Date, het lopende jaar tot nu toe). Maar deels zijn het ook momentopnamen: wat is de stand nu, of op een specifieke datum? Hoe ik dat in één rapport kan weergeven, daar moet ik ook nog even over nadenken.

Heb jij een dashboard? En zo ja, welke gegevens staan erin?

En hoe moet dat er dan uit gaan zien? Daar moet ik nog over nadenken, maar ondertussen ben ik al wel een beetje aan het ‘klooien met Excel’:

Administratie met Rabo CSV Import

Diverse lezers vroegen er al om, en zelf schreef ik er onlangs ook al over: mijn nieuwe administratiespreadsheet. En hier is ‘ie dan. Oud en vertrouwd in de interface, nieuw onder de motorkap. Tsja, opa Geldnerd houdt niet zo van interface-wijzigingen. Ik snap ook al die app-ontwikkelaars niet die bij elke nieuwe versie de gebruikersinterface volledig overhoop gooien. Waardoor ik weer enorm moet zoeken naar mijn favoriete functies. Dus zelf doe ik daar niet aan.

Zoals ik al eerder schreef is de belangrijkste wijziging dat ik nu werk met het nieuwe CSV-formaat van de Rabobank. Dit omdat ze stoppen met het TXT-formaat dat ik eerst gebruikte. De structuur waarmee ik transacties inlees is daarmee compleet gewijzigd. Dat betekent dat je eigenlijk al je transacties vanaf 1 januari 2018 opnieuw in moet lezen, in CSV-formaat.

Er zit ook een uitgebreidere gebruikershandleiding in, op het tabblad ‘Manual’. Maar leef je vooral uit op de Visual Basic macro’s die in de spreadsheet zitten. Tips, opmerkingen, aanvullingen, dingen ter verbetering van de leesbaarheid, laat het me weten in de comments of via mijn Contact-pagina!

Eén ding zit er nog niet in: ik ben bezig om een functie te bouwen die controleert of de boekingen wel op elkaar aansluiten. Maar die is nog niet foutloos, dus die zit nog niet in deze versie. Zodra ik de problemen heb opgelost verschijnt er een bijgewerkte versie. En verder wordt het in die versie ook mogelijk om bestanden van KNAB in te lezen, omdat een bloglezer zo vriendelijk is geweest mij een voorbeeld-bestand te sturen (dankjewel!).

Je kunt de nieuwe administratiespreadsheet hier downloaden.

Heb jij recent nog nieuwe dingen gedaan in Excel?

Administratiespreadsheet vernieuwen

Het begon met de mededeling dat de Rabobank ging stoppen met het bestandsformaat dat ik importeerde in mijn administratie. Het werd een code-marathon van drie dagen in februari waarin ik mijn administratie vanaf de grond opnieuw opgebouwd heb, en daarna een testperiode waarin ik ook nog allerlei kleine aanpassingen gedaan heb en nog doe. De lay-out bleef ongeveer hetzelfde, want die bevalt me. Maar de meeste macro’s werden grotendeels herschreven, en veel nieuwe functionaliteit toegevoegd.

Waarom herschrijven? Mijn administratie was de eerste spreadsheet die ik echt geprogrammeerd heb met allerlei functies en macro’s in Visual Basic. Leren programmeren door het te doen. Maar inmiddels heb ik het nodige bijgeleerd, en heb ik een aantal functies efficiënter (en dus sneller) kunnen maken. En ik heb toch nog weer een aantal handmatige handelingen weg kunnen automatiseren.

Er komen ook nieuwe functies in. Zo is de spreadsheet nu geschikt voor Rabobank én ABN AMRO, je kunt zelfs met beide naast elkaar in één spreadsheet werken. De importbestanden worden namelijk ‘genormaliseerd’ naar hetzelfde formaat. Ook is er nog steeds ondersteuning voor het importeren van je kasboekje vanuit de KashBook app op de mobiele telefoon. Ik gebruik het zelf niet zo vaak meer, want ik geef bijna geen contant geld meer uit, maar de functie was er toch.

Zit je bij een andere bank, en wil je toch gebruik maken van mijn spreadsheet? Ik ben op zoek naar voorbeeldbestanden van andere banken. De bestanden die je exporteert vanuit internetbankieren. Als je zo’n bestand hebt, wil ik de spreadsheet er wel geschikt voor maken. Laat het me maar even weten!

Verder heb ik alle instellingen bij elkaar gebracht op één pagina. Dat maakt het overzichtelijker. En er is een nieuwe rapportage waarmee je heel snel kunt zien welke uitgaven je gedaan hebt op een specifieke grootboekrekening. Maar ook mijn oude vertrouwde functionaliteit is er nog, zoals het verwerken van creditcard-betalingen.

Rond diezelfde tijd kreeg ik ook een mailtje van een gebruiker van de administratie-spreadsheet. Hij stelde mij een aantal vragen over de werking, omdat er geen handleiding bij zit. Dat laatste was (is) bewust. Mijn spreadsheets zijn vooral bedoeld ter inspiratie, om te laten zien wat er mogelijk is met Excel. Het is geen kant en klaar boekhoudprogramma, daarvoor kun je beter zoiets als WinBank of GnuCash gebruiken (beide overigens ook gratis). Je administratie bijhouden in een spreadsheet vol macro’s, waarbij je zelf niet weet hoe deze macro’s werken, is een beetje riskant. Omdat ik vrij uitgebreid gereageerd heb op de mail die ik kreeg, komt er nu wel een uitgebreidere handleiding bij de spreadsheet. Maar het blijft as-is, ik ga de spreadsheet verder niet ondersteunen.

En oh ja, de gebruikersinterface is in het Engels. Maar stilletjes denk ik na over een versie waar je uit meerdere talen kunt kiezen.

Ik ben de nieuwe versie nog aan het testen, er zitten nog een paar kleine foutjes in. Zo heb ik momenteel ruzie met de floating point error in Excel. Die zorgt ervoor dat 10,00 – 10,00 niet altijd 0,00 als antwoord oplevert. Dat kan best lastig zijn als je bepaalde controles in wilt bouwen. Maar ergens de komende weken hoop ik de tests af te ronden. En dan komt de spreadsheet uiteraard ook hier als download beschikbaar.

Hoe houd jij jouw administratie bij?

Hypotheekspreadsheet online

Regelmatig krijg ik vragen over de grafiek die ik gebruik om de stand van zaken rond de aflossing van mijn huis bij te houden, zie onderstaand. Uiteraard heb ik hiervoor een spreadsheet, of liever gezegd: een werkblad in de gezamenlijke administratie van Vriendin en mijzelf. En die wordt automatisch bijgewerkt als de maandelijkse afschrijving door de bank heeft plaatsgevonden.

Op verzoek van diverse lezers heb ik mijn hypotheek-pagina apart beschikbaar gemaakt, je kunt ‘m hier downloaden. Hij maakt gebruik van twee eenvoudige Visual Basic functies, die uiteraard ook zijn inbegrepen in de spreadsheet. En de spreadsheet bevat ook mijn grafiek.

Hoe houd jij jouw hypotheek en aflossingen bij?

Spreadsheet Dag

Dinsdag 17 oktober is Spreadsheet Dag. Echt Waar. Kijk maar hier. Ter herinnering aan de datum waarop VisiCalc, het eerste spreadsheet programma voor personal computers, werd uitgebracht – 17 oktober, 1979. Dat was overigens voor de legendarische Apple II computer.

Spreadsheets hebben het organiseren van, bewerken van, en rekenen met grote hoeveelheden gegevens vereenvoudigd en toegankelijk gemaakt voor gewone stervelingen zoals jij en ik. Ik zou mijn administratie, vermogensbeheer en beleggingen niet kunnen doen zonder. Het zou eenvoudigweg teveel tijd kosten.

Ik neem vanavond een glaasje whiskey om op de gezondheid van mijn spreadsheets te drinken. Lang leve Spreadsheet Dag!

Hoe belangrijk zijn spreadsheets voor jou?

Stand van het huis

Of eigenlijk kan dit blogje ook wel ‘Klooien met Excel – deel zoveel’ heten. In mijn spreadsheet zit uiteraard ook een tabblad waarop ik de stand van de aflossing van het huis bijhoud. En onder het motto ‘een plaatje zegt meer dan duizend cijfers’ heb ik daar ook een grafiekje voor gemaakt. Waarin ik keurig kan zien hoeveel eigen geld we in het huis gestoken hebben, wat we inmiddels hebben afgelost, en wat er nog aan hypotheek resteert. De grafiek wordt uiteraard automatisch bijgewerkt als er weer een betaling naar de hypotheek is gegaan.

Voor de huisjesvorm van de grafiek heb ik gebruik gemaakt van deze tip.

Hoe staat het met jouw huis?

Valutakoersen automatisch bijwerken

Geldnerd is eigenlijk heel lui. Maar werkt soms ook heel hard om die luiheid naar nieuwe hoogten te doen stijgen. Zo ook afgelopen week. Jullie kennen mijn voorliefde voor spreadsheets. Alles automatisch, met één druk op de knop. En daar ga ik steeds een stukje verder in.

Bij de eerste versie van mijn beleggingsspreadsheet moest ik elke transactie handmatig invoeren. Inmiddels experimenteer ik met een versie waarin ik gewoon de geldtransacties en beleggingstransacties importeer. Die kan ik downloaden bij mijn broker. De macro’s zijn nog niet 100% perfect, maar de meeste problemen zijn nu wel opgelost.

Daar zat nog wel één handmatig dingetje bij waar ik mij aan stoorde. Niet alle fondsen waar ik in handel zijn genoteerd in Euro’s. Er zijn er ook die in Amerikaanse dollars genoteerd zijn. Of nog in andere valuta. En omdat ik mijn beleggingen in Euro’s bijhoud, moest ik dan bij iedere transactie de actuele wisselkoers invullen. En die moest ik opzoeken. Gewoon, handmatig. Op een website zoals deze.

Tsja, en dat kan natuurlijk niet. Dat moet handiger. Automatisch. Het heeft me een uurtje of vier gekost, maar het is gelukt. Ik heb gezocht naar een website die via een API benaderbaar is. Een API is een Application Programmable Interface, oftewel een soort ‘stekkerdoos’ op een website die je door een ander programma (bijvoorbeeld mijn spreadsheet) kunt laten benaderen. Die zijn er verschillende. Maar ik zocht (uiteraard) naar een gratis en liefst open-source variant. Ik vond deze, fixer.io. Die verstrekt de gegevens in JSON format (JavaScript Object Notation), een relatief eenvoudige methode voor gegevensuitwisseling.

Dat is handig. Als je bijvoorbeeld de code

http://api.fixer.io/2017-05-12?symbols=USD

naar de website stuurt, krijg je de volgende respons:

{"base":"EUR","date":"2017-05-12","rates":{"USD":1.0876}}

In gewone mensentaal: op vrijdag 12 mei 2017 was de wisselkoers van € 1,00 in Amerikaanse dollars USD 1,0876. Ik heb eerst nog geprobeerd om die respons keurig in te lezen als JavaScript, maar dat werd me al gauw te ingewikkeld. Ik wil daar ook geen extra tooltjes of zo voor installeren, het moet gewoon werken in ‘standaard’ Excel. Dus heb ik besloten om het antwoord van de API gewoon als String in te lezen, en de koers eruit te ‘knippen’. In onderstaande code doe ik dat voor de duidelijkheid nog even in drie stappen, maar je kunt het ook in één stap doen.

Function GetExchangeRate(Datum As Date, toCurr As String) As Double

Dim TempDate As String
Dim qurl As String
Dim TempString As String
Dim TempString2 As String
Dim TempString3 As String

TempDate = CStr(Format(Datum, "yyyy-MM-DD"))
qurl = "http://api.fixer.io/" & TempDate & "?symbols=" & toCurr

TempString = Application.WorksheetFunction.WebService(qurl)
TempString2 = Right(TempString, 8)
TempString3 = Left(TempString2, 6)

GetExchangeRate = CDbl(TempString3 / 10000)

End Function

En dat werkt. Nu hoef ik niet meer handmatig naar valutakoersen te zoeken. Voor de “toCurr” kun je elke valuta gebruiken die Fixer accepteert. Mijn nerdhart is weer blij, ook al weet ik zelf ook wel dat ik uren heb besteed aan iets wat mij nog geen minuut per transactie kostte…

Heb jij nog mooie functies in elkaar geknutseld?

Nieuwe versie beleggingsspreadsheet

Er staat weer een verbeterde versie van de beleggingsspreadsheet online. Met ondermeer een verbeterde draaitabel, waardoor het bestand een stuk kleiner is geworden. Daarnaast is ook de Fund-pagina verbeterd. Je ziet nu in één grafiek de koers en de waarde van het fonds, op twee verschillende y-assen. Dat heeft me nog best wat moeite gekost om het werkend te krijgen, maar het is gelukt!

Je vindt ‘m op de Downloads-pagina. Veel plezier ermee!

Nieuwe versie beleggingsspreadsheet

Afgelopen weekend heb ik een nieuwe versie van mijn beleggingsspreadsheet online gezet. Hierin zijn diverse bugs gerepareerd. Zo ging het mis met importeren als er dividenduitkeringen in het weekrapport zaten. Ook zit de nieuwe Fund-pagina erin. Hier kun je per fonds de hele historie in jouw portefeuille zien.

Veel plezier ermee!

Older posts

© 2018 Geldnerd.nl

Theme by Anders NorenUp ↑