In a recent project I needed the range of the Grand Totals in any pivottable in Excel. Unfortunately the PivotTable object has no property for this. Solutions I found on the Internet were for specific cases. So I developed a generic solution that I want to share with you.
[Read more…] about The Grand Totals range of a pivottable in ExcelReplace drill-through for Power Pivot and Power BI pivottables in Excel
Excel’s default drill-through for pivottables connected to a datamodel or Power BI dataset is often not very helpful. The add-in PowerDrill for Excel replaces Excel’s drill-through and gives you full control over the drill details.
[Read more…] about Replace drill-through for Power Pivot and Power BI pivottables in ExcelAuditfileValidator for Excel: validate and inspect Xml auditfiles
AuditfileValidator is an Excel addin that validates the most common auditfile formats and versions (XAF, XAS, XAA, SAF-T, etc) against their schema, and presents an overview of the most important properties of the auditfile.
[Read more…] about AuditfileValidator for Excel: validate and inspect Xml auditfilesSAF-T auditfiles for test and development
Most test and demo auditfiles that are available on the internet are incomplete and invalid. In this post I present a maximal SAF-T auditfile which is valid and contains all possible data elements. I also explain how to create this file and it’s minimal variants.
[Read more…] about SAF-T auditfiles for test and developmentEen postcodetabel op basis van BAG Extract 2.0
In de Basisregistratie Adressen en Gebouwen (BAG) beheert het Kadaster alle adressen die in Nederland voorkomen. In de BAG wordt bij een adres ook de postcode van PostNL vastgelegd, en dus moet het mogelijk zijn om o.b.v. de BAG een postcodetabel van Nederland samen te stellen.
[Read more…] about Een postcodetabel op basis van BAG Extract 2.0Improvements on SAF-T, OESO’s Standard Audit File for Tax
SAF-T (Standard Audit File for Tax) is an international standard for electronic exchange of accounting data. In this post I propose some improvements for SAF-T version 2.00.
[Read more…] about Improvements on SAF-T, OESO’s Standard Audit File for TaxPower BI ontsluit specificatie ISO standaard Audit Data Collection
In dit artikel beschrijf ik een Power BI app die de specificatie ontsluit van ISO standaard 21378 Audit Data Collection. Deze standaard is beschreven in een pdf document dat als bron heeft gediend voor de Power BI app. De app maakt de informatie toegankelijker en biedt de mogelijkheid om deze op verschillende manieren te benaderen.
[Read more…] about Power BI ontsluit specificatie ISO standaard Audit Data CollectionXAF Explorer voor Power BI
XAF Explorer voor Power BI toont de volledige inhoud van een XML Auditfile Financieel (XAF) en voert diverse controles uit. XAF Explorer is beschikbaar als template en ondersteunt de XAF versies vanaf 3.0.
[Read more…] about XAF Explorer voor Power BIRGS en XAF: bevindingen
In de afgelopen weken heb ik onderzoek gedaan naar het Referentie Grootboekschema RGS en de combinatie van RGS met de XML Auditfiles, met name XAF 3.2. Mijn bevindingen heb ik beschreven in twee openbare Power BI sites.
[Read more…] about RGS en XAF: bevindingenGlobale straling in Power BI
Om te bepalen of je zonnepanelen goed presteren kun je de gemeten opbrengst in een periode vergelijken met de theoretisch maximaal haalbare opbrengst. Deze verhouding zou altijd constant moeten zijn. Voor het bepalen van de maximale opbrengst moeten we de globale straling weten. Hiervoor is nu een openbare Power BI app beschikbaar.
[Read more…] about Globale straling in Power BIHow to set Excel’s ActivePrinter using VBA?
Recently I wanted to change Excel’s ActivePrinter using VBA. That isn’t as straightforward as I expected it to be. In particular there is a localization issue.
[Read more…] about How to set Excel’s ActivePrinter using VBA?Normalize data: from Userfriendly to Pivotfriendly
If possible I always use pivottables to create reports in Excel. Pivottables need well formed, normalized data tables as source, which are usually not suitable for data entry. In this post I describe two techniques to bridge the gap between userfriendly and pivotfriendly data structures.
[Read more…] about Normalize data: from Userfriendly to Pivotfriendly
Pivottable data connection models
The way a datasource connects to a PivotTable depends on the datasource. In this post an overview of the six connection models that you can create by means of Excel’s userinterface. [Read more…] about Pivottable data connection models
Excel and XML: four observations and two bugs
I have been working with Xml files in Excel for some time now, and thought it is time to share some observations. [Read more…] about Excel and XML: four observations and two bugs
Create Xml from Schema
In this post I describe a tool to create an Xml file from a schema (Xsd) using VBA and the DOM and SOM objects of the Microsoft XML library MSXML version 6. [Read more…] about Create Xml from Schema
Get XPaths from workbook – update
In Get XPath objects from Excel workbook I described a method to retrieve all mapped XPaths from a workbook. In this post I describe another method, using the Worksheet.XmlMapQuery function. It’s simpler, and faster.
[Read more…] about Get XPaths from workbook – updateIBAN validation with BBAN format check
In this post I describe Excel/VBA functions for IBAN validation that not only perform a Mod 97 calculation but also validate the format of the country-specific Basic Bank Account Number (BBAN). [Read more…] about IBAN validation with BBAN format check
Sepa Direct Debits: from Excel to Pain
Europe is preparing for SEPA, the Single Euro Payments Area. One of the SEPA goals is to replace all national direct debit standards by one XML based SEPA standard by 1 February 2014. This article shows how to create such an Xml file from Excel, without scripting. [Read more…] about Sepa Direct Debits: from Excel to Pain
Get XPath objects from Excel workbook
When working with XML enabled workbooks the relevant objects and properties are easy to retrieve. Unfortunately a workbook has no built-in collection of XPath objects. How can we get a list of all XPath objects in a workbook? [Read more…] about Get XPath objects from Excel workbook
Mastermind in Excel
In this post an Excel worksheet implementation of the well known Mastermind game. I developed the Mastermind part of this workbook several years ago. For this showcase app I added a scoreboard, and turned it into a so called dictator application. [Read more…] about Mastermind in Excel