Verder met mijn spreadsheets

Inmiddels een week geleden heb ik mijn administratiespreadsheet gepubliceerd. In eerste instantie met een bugje, maar die is hersteld. De nodige mensen hebben ‘m inmiddels gedownload. En via de mail zijn er ook al een aantal vragen binnengekomen.

Om maar met de meest gestelde vraag te beginnen: nee, er komt geen uitgebreide handleiding. Ten eerste omdat het mij eenvoudigweg aan de tijd ontbreekt om die te schrijven. Maar ik denk ook dat ik niet in staat ben om een goede handleiding te schrijven. Ik ken de spreadsheet en de macro’s van haver tot gort, want ik heb ze zelf gebouwd, letter voor letter. En heb dus geen idee waar mensen tegenaan lopen die ‘m gewoon gaan gebruiken.

Maar het zou ook voorbijgaan aan het doel wat ik heb met het publiceren van de spreadsheet. Het is geen kant en klare administratie die je zomaar kan gebruiken. Daarvoor vind ik het ook te riskant om met macro’s te werken als je ze niet helemaal doorgrondt en problemen niet zelf kunt oplossen. Wil je je niet verdiepen in Excel en Visual Basic, neem dan liever een kant en klaar pakket zoals YNAB of WinBank.

Ik publiceer mijn spreadsheet vooral om mensen te laten zien wat er kan met Excel. En om mensen inspiratie te geven voor het aanpassen van hun huidige spreadsheet. Daarom ben ik ook zo benieuwd naar de spreadsheets van anderen, omdat ik ook graag nieuwe ideeën opdoe.

Dus, geen handleiding. Maar ik zal best nog wel wat meer schrijven over hoe de spreadsheet in elkaar zit, en ook vragen zal ik wel beantwoorden. Ook als ik binnenkort mijn (nog veel uitgebreidere) beleggingsspreadsheet online zet.

Heb jij al naar mijn administratiespreadsheet gekeken?

Tussendoor-spreadsheet-klooi-blogje

  • Berichtcategorie:ICT

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

  • Berichtcategorie:ICT

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

  • Berichtcategorie:ICT

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?