Geldnerd.nl

Blog over (financieel) bewust leven

Tag: vba (page 1 of 3)

Stoppen met mijn spreadsheets

“Als ik mijn spreadsheets kan migreren van Excel/Visual Basic naar een open(-source) en toekomstvaster systeem, dan kan ik van Excel en dus van Microsoft Office af. Als ik van Microsoft Office af kan, dan kan ik waarschijnlijk ook van Windows 10 af.” Die gedachte schoot door mijn hoofd toen ik bezig was met de evaluatie van mijn privacy- en beveiligingsmaatregelen in Operatie Data Footprint.

Ik besef al een tijdje dat ik, vooral door de spreadsheets die gebruik maken van Visual Basic (VBA) macro’s, effectief ‘vast’ zit aan Microsoft Office. Ooit heb ik geprobeerd om de spreadsheets te migreren naar LibreOffice. Maar dat bleek te arbeidsintensief, het was geen migreren maar volledig opnieuw bouwen. De administratie is grotendeels herbouwd (maar nooit in gebruik genomen), aan mijn beleggingsspreadsheet ben ik nooit begonnen.

Ik maak een onderscheid in spreadsheets met macro’s en zonder macro’s. Zonder macro’s is het geen probleem, die spreadsheets werken allemaal ook in open-source office pakketten zoals LibreOffice. Spreadsheets met macro’s is het issue. Een korte inventarisatie leerde mij dat ik macro’s gebruik in:

  1. Mijn administraties, sinds 2013, voor mijzelf en voor de gezamenlijke huishouding met Vriendin. Deze telt als eentje, omdat ze hetzelfde format hebben. Migreer ik er een, dan kan ik ze allemaal migreren.
  2. Mijn beleggingsspreadsheet, met de historie sinds 2013.
  3. Mijn transactie-converter, die kas- en beleggingstransacties van mijn broker omzet naar het format van de beleggingsspreadsheet.
  4. De hypotheekspreadsheet met de hypotheekgegevens sinds het begin in 2016.
  5. Het financiële dashboard.
  6. De FIRE Calculator.

Mijn spreadsheets zijn het hart van mijn financiële huishouding. Ik kan me niet meer voorstellen dat ik mijn administratie en beleggingen en hypotheek NIET meer bij zou houden. Tegelijk is mijn stelsel van spreadsheets stevig gegroeid en tijdrovend geworden. Niet om bij te houden, de meeste gegevens worden automatisch geïmporteerd en verwerkt. Maar wel om steeds maar uit te breiden en nieuwe functies bij te bouwen. Een leuke, maar ook best tijdrovende hobby.

“Misschien moet ik toch….” was de tweede gedachte in mijn hoofd. Toch wat? Toch serieus gaan kijken naar een softwarepakket om mijn financiën te managen. Ik ben verwend, met mijn spreadsheets die helemaal naar mijn wens zijn. Maar het zou natuurlijk wel veel tijd schelen! Ik kan me niet voorstellen dat ik mijn administratie en beleggingen en hypotheek NIET meer bij zou houden. Ik kan me WEL voorstellen dat ik dat niet meer in spreadsheets doe, maar in een softwarepakket.

Wat zijn dan zo globaal mijn eisen aan deze software? Ik wil mijn beleggingen, hypotheek en administratie in één integrale omgeving kunnen voeren. Met automatische importmogelijkheden van banktransacties en aandelentransacties, en mogelijkheden om een kasboek en creditcard bij te houden. Mijn huidige data wil ik migreren. Er moeten voldoende rapportagemogelijkheden zijn, ik moet zelf rapportages en grafieken in kunnen richten. Die ik ook op mijn blog kan gebruiken. En idealiter is de software open-source. De gegevens wil ik in eigen beheer, niet in de cloud. En de software moet beschikbaar zijn op verschillende platformen, in elke geval Windows 10 en Linux. Voor mijn eigen situatie zou de software moeten kunnen werken met de Rabobank, ABN AMRO, Binck en Nationale Nederlanden.

Door de open-source eis vallen bekende oplossingen als YNAB (You Need A Budget) al af. Ook een Nederlandse oplossing als WinBank voldoet niet aan de eisen. Veel Amerikaanse FIRE bloggers gebruiken Mint en/of Personal Capital, maar ook dat voldoet zeker niet aan mijn eisen. Ik kom eigenlijk uit bij twee mogelijke kandidaten. GnuCash, een open-source oplossing die ik al vaker genoemd heb op dit blog, en Firefly III, een oplossing die je op een eigen server kunt draaien. Bij die laatste vind ik het draaien op een server ingewikkeld, het nut daarvan zie ik wel voor een adresboek of agenda, maar niet voor mijn administratie. En Firefly lijkt afhankelijk van één persoon, terwijl GnuCash een ‘groepsding’ is.

Ik heb ook nog even gekeken in de lijst met ‘huishoudboekjes’ van Wijzer in Geldzaken. Maar daar vind ik vooral dure commerciële oplossingen. Die dus ook zeker niet aan mijn eisen voldoen.Dus. Ik ga de komende periode maar eens kijken of ik GnuCash naar mijn wensen in kan richten.

Gaat het dan echt gebeuren? ‘Mr. Excel’, Geldnerd de spreadsheetkoning, van de spreadsheets af? Wordt vervolgd…

Heb jij misschien ervaring met GnuCash?

error

Migratie beleggingsspreadsheet en import De Giro toegevoegd

Van een paar mensen kreeg ik de vraag hoe je kunt migreren van mijn oude beleggingsspreadsheet (versie 2) naar de nieuwe (versie 3). Met het schaamrood op mijn kaken moet ik bekennen dat ik daar niet over had nagedacht. Ik heb mijn versie 2 gewoon doorontwikkeld. Maar er is wel veel veranderd, dus als je in één keer overgaat van de oude naar de nieuwe versie zijn er iets meer stappen die je moet zetten. Vandaar dit bericht.

Er staat nu een nieuwe versie online. Die is voorbereid op deze migratie. En er zit nog iets nieuws in. Je kon geen bestanden van De Giro importeren. Daar was een heel simpele reden voor. Ik ben er namelijk geen klant. En dus heb ik ook geen voorbeeldbestand van hun portefeuillerapportages. Zo’n voorbeeldbestand heb ik wel nodig om een importmacro te kunnen bouwen en testen. Maar inmiddels is dat opgelost, met dank aan lezer Ruud. Die heeft me zo’n rapportage gestuurd. En dus kun je nu ook bestanden van de Giro importeren. Waarom ik zo’n functie bouw als ik ‘m zelf niet ga gebruiken? Omdat het kan! En omdat anderen er misschien iets aan hebben.

Dividenden

Bij Alex en Binck worden meldingen van nieuwe dividenden ook in de weekrapportage geplaatst. Die verwijder ik dan automatisch tijdens het importeren, want ik heb ze niet nodig. Ze zijn pas relevant als het dividend daadwerkelijk uitbetaald wordt, dat is een Fund Transaction van het type Dividend Cash. Hoe dat werkt bij De Giro weet ik helaas nog niet, want daar zat geen voorbeeld van in het bestand dat ik van Ruud ontving. Het kan dus zijn dat het importmacro daarop vastloopt. Maar als iemand hier meer informatie over heeft, zal ik het importmacro zo snel mogelijk aanpassen.

Je kunt de nieuwe versie hier downloaden , of vinden op mijn Downloadspagina.

Migratie

Onderstaand een stappenplan voor de migratie:

  • Maak een back-up van de oude versie, voor het geval er iets mis gaat bij het overzetten…. Sowieso is regelmatig back-ups maken natuurlijk een goed idee.
  • Zet de gedownloade versie in de juiste directory en pas naar wens de bestandsnaam aan.Zet vervolgens de oude versie (2) en de nieuwe versie (3) tegelijk open.
  • Werk de instellingen in de nieuwe versie (3) bij, met name de directorynamen die je nodig hebt, en de directory en naam van de spreadsheet zelf.
  • Verwijder de fondsen die eventueel nog op het werkblad ‘Settings’ in de nieuwe versie (3) staan.
  • Verwijder in de nieuwe versie (3) op het werkblad ‘Charts’ alle fondsen op de lijst. Dat zorgt ervoor dat straks de lijst opnieuw gegenereerd wordt.
  • Verwijder uit de nieuwe versie (3) de volgende werkbladen: Cash, Transactions, FundValue en FundHistory.

Verwijderen doe je door met de rechtermuisknop op de naam van het werkblad te klikken in de nieuwe versie (3), en de optie Verwijderen (Remove) aan te klikken.

  • Kopieer uit oude versie (2) de onderstaande tabbladen naar de nieuwe versie (3): Cash, Transactions, FundValue en FundHistory.

Kopieren doe je door met de rechtermuisknop op de naam van het werkblad te klikken in de oude versie (2), en de optie ‘Verplaatsen of Kopiëren’ aan te klikken. Kies bij ‘Naar map’ de naam van de nieuwe versie (3) en vergeet niet de optie ‘Kopie maken’ aan te vinken. In welke volgorde je de werkbladen neerzet maakt niet zoveel uit, de macro’s werken met de precieze naam van het werkblad, niet met de volgorde.

Verder heb ik een migratie-macro gemaakt voor de laatste stappen. De knop hiervoor vind je op het werkblad ‘Settings’.

  • Op het werkblad ‘Cash’ heb ik een wijziging aangebracht. De kolommen ‘1 EUR =’ (wisselkoers) en ‘Remarks’ zijn in de nieuwe versie omgedraaid.
  • Opnieuw opbouwen van het werkblad TotalValue, waar per week een aantal totaaltellingen en andere indicatoren worden opgeslagen
  • Opnieuw opbouwen van het werkblad Portfolio, waar per fonds een aantal indicatoren worden bijgehouden

Hierna zou ik eerst even de spreadsheet opslaan en Excel afsluiten, en daarna opnieuw Excel starten en de spreadsheet openen voordat je verder iets doet! De laatste stap is dan een Refresh en Reformat van de draaitabel (Pivot) op het werkblad ‘Actual’.

Bug…

Ik heb nog wel een bug ontdekt. De spreadsheet kan niet omgaan met een situatie waarin er nog nooit een kolom met koersen is ingevoerd. Dat is een situatie die je alleen tegenkomt als compleet nieuwe gebruiker. Ik moet er nog eens even goed over nadenken hoe ik dat op kan lossen.

Update: Er zat nog een bugje in, waardoor het veld met de APIkey voor de wisselkoersen niet herkend werd. Dat is inmiddels opgelost.

Klooi jij ook graag met spreadsheets?

error

Beleggingen beter bijhouden

De versie van mijn beleggingsspreadsheet die je hier kunt downloaden dateert alweer van mei 2017. Hoog tijd dus voor een update, want ik heb er sindsdien veel functionaliteit aan toegevoegd. Vanaf heden kun je dus een nieuwe versie downloaden. Het staat je vrij om de spreadsheet of mijn code te (her)gebruiken, maar ik lever er geen actieve ondersteuning op. Dit is dus vooral bedoeld voor andere Excel-nerds, en om te laten zien wat er allemaal kan in Excel. In deze blogpost een overzicht van de belangrijkste nieuwe functies.

Advisor

Ik heb een apart tabblad ‘Advisor’ toegevoegd. Op deze tab geef je de fondsen aan van de door jou gewenste portefeuille, en de percentages die je in elk fonds wilt aanhouden. De Advisor kijkt dan op basis van de actuele waarde welke fondsen je bij zou moeten kopen om dichter bij de door jou gewenste verdeling te zitten. Die gebruik ik elke maand, ik wil zo min mogelijk tijd kwijt zijn aan het inleggen. Dan zou ik maar kunnen gaan twijfelen…

Trendindicator

Verder heb ik een Technische Indicator ingebouwd die je laat zien wat de langduriger trend van het fonds is. Daarvoor gebruik ik het 200-daags en het 50-daags voortschrijdend gemiddelde. De trend is positief (groen pijltje omhoog) als het 50-daags voortschrijdend gemiddelde groter is dan het 200-daags voortschrijdend gemiddelde. De trend is negatief (rood pijltje omlaag) als het 50-daags voortschrijdend gemiddelde kleiner is dan het 200-daags voortschrijdend gemiddelde. Dit heeft natuurlijk beperkte waarde in een buy-and-hold strategie, maar ik vind het wel leuk om een idee te hebben hoe de fondsen ervoor staan.

Grafiek Inleg versus Waarde

Wat ik zelf een erg leuke toevoeging vind, is de Grafiek ‘Inflow vs Value’. Hierin laat ik voor een zelf te kiezen periode de waarde van mijn portefeuille zien, maar ook het bedrag dat ik tot op heden heb ingelegd. Als de actuele waarde hoger is dan de totale inleg, is het verschil een groen vlak (want dan heb ik ‘op papier’ winst gemaakt). Is de actuele waarde lager dan de totale inleg, dan is het verschil een rood vlak (want dan heb ik ‘op papier’ verlies gemaakt). Dat laatste heb ik gelukkig de afgelopen 5 jaar niet meer meegemaakt. Deze grafiek zorgt ervoor dat ik niet meteen nerveus wordt als de koersen eens een paar weken dalen. Het groene vlak wordt dan weliswaar kleiner, maar ik heb nog steeds ‘papieren winst’.

Instellingen

Verder heb ik het werkblad ‘Settings’ een beetje opgeruimd, dat was door de jaren heen een rommeltje geworden. Ik houd van orde en netheid, en die is er nu weer.

Ook is er een aanpassing aan de functie GetExchangeRate , waarmee je de wisselkoers van allerlei valuta op elke mogelijke dag kunt ophalen. Tegenwoordig heb je een API-key nodig, die je op de website zelf (gratis) kunt aanmaken. Die API-key is ook een instelling op het ‘Settings’ werkblad.

Klaarmaken voor Publicatie

Tsja, en ik heb ook nog even nagedacht waarom ik zo lang geen nieuwe versie heb geplaatst. Dat komt deels omdat het een behoorlijke handmatige klus is om de spreadsheet voor te bereiden voor publicatie. Ik haal daarvoor mijn persoonlijke gegevens eruit, en die vervang ik door dummy-data. Dat kost behoorlijk wat tijd, en is niet de leukste klus. Je snapt het al…. Ik heb daar nu een macro voor gebouwd, die dit werkje grotendeels automatisch uitvoert. Daarmee kost het mij minder tijd om nieuwe versies voor te bereiden. Deze macro zit overigens niet in de versie die ik publiceer.

Je kunt de nieuwe versie downloaden , en deze is uiteraard ook te vinden via mijn Downloads pagina.

Hoe houd jij jouw beleggingen bij?

error

Programmeer-seizoen is begonnen!

Seizoenen zijn toch een prachtig iets. In het Verre Warme Land waren ze er niet echt. Het was er 365 dagen per jaar zonnig en warm. Ja, er waren maanden waarin het nog warmer was dan normaal, er waren maanden met meer of minder wind, en er waren zelfs maanden met iets meer kans op regen dan normaal, maar dat was het dan ook wel. Nee, dan onze Nederlandse seizoenen! Alhoewel ze dit jaar natuurlijk wel een beetje in de war zijn geweest, met de lange, warme en vooral droge zomer. Die ook nog eens duurde tot ver in oktober!

Wisseling van seizoenen is altijd aanleiding voor veel discussie. Je houdt juist wél van de lente / zomer / herfst / winter *, of je houdt juist absoluut niet van de lente / zomer / herfst / winter * (* = doorhalen wat niet van toepassing is). Er horen rituelen bij, zoals de voorjaarsschoonmaak, of het wisselen van de zomer- en winterkleding. We eten verschillende dingen (stamppot, jummie!). Kortom, er verandert veel in onze leventjes bij de wisseling van de seizoenen.

De afgelopen maanden heb ik me natuurlijk ook beziggehouden met een ‘zomerproject’: de aanleg van onze tuin. Daar is toch best veel tijd in gaan zitten. En ook op mijn werk was (is) het een drukke tijd. Dat betekent dat andere hobby’s eronder geleden hebben.

Mijn postzegelverzameling is bijvoorbeeld niet bijgewerkt. En ook mijn sigarenbandjesverzameling loopt hopeloos achter.

Zo heb ik deze zomer nauwelijks naar mijn spreadsheets omgekeken. Ja, ik heb de beleggingsrapportage wekelijks gedownload en verwerkt. En eens in de paar weken de financiële administratie. Maar er is nauwelijks geprogrammeerd. Terwijl ik zoveel ideeën had en heb. Mijn integrale dashboard is nog steeds geen stap verder. En ook mijn beleggingsspreadsheet is dringend aan een grote onderhoudsbeurt toe. Ik heb allerlei ideeën voor nieuwe grafieken, functies en rapportages. Die heb ik allemaal verzameld, als ik een idee heb dan schrijf ik het op. Die lijst is inmiddels behoorlijk lang.

Het enige programmeerprojectje dat ik wel heb afgerond, is de eerste versie van mijn FIRE Calculator voor Loonslaven. Maar ook daarvoor heb ik nog een stapeltje e-mails en berichtjes met verzoekjes tot verbetering/verandering en foutmeldingen. Sorry, beste lezers die de moeite nemen om te reageren! Maar ik ben er nog niet aan toegekomen.

Maar er is hoop. Want nu de bladeren vallen en het buiten kouder wordt, is het weer tijd voor binnenactiviteiten. Oftewel: het programmeerseizoen is weer begonnen. Waar de een het heerlijk vindt om onder een dekentje met een kop thee voor de televisie te zitten, of een boekje te lezen, vind ik het daarnaast ook heerlijk om me een aantal uren vast te bijten in een puzzel. Oftewel: al die ideeën te vertalen in programmacode die werkt. Daarnaast heb ik ook nog steeds mijn voornemen om JavaScript te leren, daar wil ik dit komende programmeerseizoen ook mee verder. En ook Python wil ik uit gaan proberen. Ik heb er zin in!

Wat verandert er bij jou bij de wisseling der seizoenen?

error

Valutakoersen automatisch bijwerken

Update: In juni 2018 is de API aangepast. En mijn macro dus ook. De nieuwe versie vind je in dit bericht.

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?

error

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!

error
« Older posts

© 2019 Geldnerd.nl

Theme by Anders NorenUp ↑