---

---

Het CBS en Excel kennen allebei het Open-Data-protocol; daar gaan we gebruik van maken. In onderstaand artikel allereerst daarom een korte uitleg van Open Data.

Open Data

Om gegevens tussen systemen te kunnen uitwisselen moeten uiteraard de vorm en inhoud hiervan bekend zijn. Wil je de uitwisseling automatiseren dan moeten er harde afspraken over die vorm en inhoud gemaakt worden.

In dit geval bepaalt het CBS de inhoud (welke gegevens worden beschikbaar gesteld, hoe vaak et cetera), maar kunnen we zelf op hun website min of meer de lay-out en vorm van uitwisselen bepalen (zoals we in het vorige artikel zagen).

Het CBS ondersteunt echter ook het zogenaamde Open Data-protocol (via zogenaamde API‘s). Dit is een set van afspraken waardoor de aanleverende en de ontvangende partij precies weten wat de vorm van de aangeleverde gegevens is. Ook Excel (als ontvanger) ondersteunt dit protocol.

Op dit moment gaat het CBS over van versie 3 naar versie 4 van het OData-protocol (zie www.cbs.nl/../open-data en www.cbs.nl/../statline-als-open-data). Excel kan met allebei de versies overweg.

Power Query

Wanneer je in een CBS-Statline-scherm op de Download-button klikt, zie je dat er ook een mogelijkheid is om naar het Dataportaal te gaan:

Op deze pagina kun je verschillende datasets downloaden en informatie over de (verwerking van) Open Data vinden. Voor het verder automatiseren in Excel hebben we alleen de Identifier nodig (in dit geval 85264NED):

We hebben nu een zogenaamd genormaliseerde tabel van het CBS binnengehaald. Een nadeel is dat de tabel niet een zodanige vorm heeft dat je daar gemakkelijk een draaitabel ‘op los kunt laten’. Daar moeten we nog wat aan doen!

NB1 het ophalen van een Open-Data-tabel vanuit de CBS-website gaat altijd via de URL opendata.cbs.nl/ODataFeed/odata/Identifier, waarbij je zelf de juiste Identifier dient op te geven.

NB2 de query en de Excel-tabel krijgen automatisch de naam TypedDataSet.

Power Query 2

In het tabblad Power2 in het Voorbeeldbestand hebben we op dezelfde manier als hiervoor de tabel TypedDataset opgehaald.
Maar omdat we alleen geïnteresseerd zijn in jaar-cijfers zijn de op te halen rijen gefilterd:

NB de naam van de query en de Exceltabel worden TypedDataSet (2) respectievelijk TypedDataSet_2, omdat de naam TypedDataSet al voorkomt.

Wanneer je nu een draaitabel op basis van de tabel maakt moet je wel goed uitkijken welke selectie je maakt:

De tabel staat nog vol met codes; dat is ook niet wat we willen.
Het CBS heeft ieder Open-Data-item netjes uitgenormaliseerd: “Normalisatie is het proces van het ordenen van gegevens in een database. Dat betreft het maken van tabellen en het leggen van relaties tussen die tabellen op basis van regels die als doel hebben de gegevens te beveiligen en de database flexibeler te maken door redundantie en inconsistente afhankelijkheid te voorkomen“.

Willen we dus af van al die codes dan zullen we ook de gerelateerde tabellen moeten binnenhalen.

Power Query 3

We gaan opnieuw de Arbeidsdeelname, kerncijfers ophalen:

1. kies in de menutab Gegevens de optie Gegevens ophalen/Van andere bronnen/Uit OData feed
2. in het vervolgscherm wordt gevraagd naar de URL; dat is in dit geval ook weer opendata.cbs.nl/ODataFeed/odata/85264NED
   Klik OK.
3. plaats een vinkje bij Meerdere items selecteren
4. vink alle tabellen aan behalve UntypedDataset en kies Gegevens transformeren
   
5. alle aangevinkte tabellen worden tegelijkertijd in Power Query binnengehaald
   
6. nu gaan we de codes in de kolom Geslacht “vertalen”:
   * kies binnen de menutab Start de optie Query’s samenvoegen
   * vul het vervolgscherm als volgt in:
   
   * klik OK
   * klik dan op de 2 pijltjes in de kop van de nieuwe kolom achteraan
   
   * zorg dat in het vervolgscherm alleen Title is aangevinkt en klik OK

10. we sluiten Power Query, maar let op: kies Sluiten en laden naar … en zorg dat er alleen een verbinding gemaakt wordt:
    
    Op deze manier blijven alle gegevens in het geheugen, maar worden niet nog eens dubbel in Excel zelf vastgelegd.

Als voorbeeld maken we een draaitabel:

1. open een nieuw tabblad
2. selecteer de cel waar de draaitabel moet komen
3. kies in de menutab Invoegen de optie Draaitabel
4. in het volgende scherm staan alle opties goed:
   
   Excel kiest als basis voor de draaitabel het Gegevensmodel.

Twee andere voorbeelden, die je nu snel op basis van de CBS-gegevens kunt maken:

en

Zijn de gegevens bij het CBS gewijzigd of aangevuld? Kies in de menutab Gegevens de optie Alles vernieuwen en alle overzichten en grafieken worden automatisch aangepast!

---