---

---

Maar wijzigen de gegevens op een internet-pagina periodiek dan moeten we die actie iedere keer opnieuw uitvoeren, inclusief eventuele extra berekeningen etc.

In de afgelopen jaren heeft G-Info al diverse methoden besproken (zie bijvoorbeeld Excel en het World-Wide-Web en MS-Query: een alternatief voor Vert.Zoeken), maar de opties die daar besproken zijn, zijn door Microsoft bij het opleveren van nieuwe versies steeds meer gesloopt.
Maar niet getreurd: we hebben daar Power Query voor teruggekregen; vanaf versie 2016 Gegevens ophalen en transformeren genoemd.

Hieronder enkele ‘simpele’ voorbeelden, maar ook een uitleg hoe je iedere willekeurige internet-pagina kunt uitlezen.
Volgende keer zullen we kijken hoe je gegevens die over verschillende pagina’s verspreid staan, kunt overhalen naar Excel.

Inwoneraantal per land

Hoe haal je deze gegevens binnen in Excel?

1. open een nieuw werkblad
2. kies in de menutab Gegevens in het blok Gegevensophalen en transponeren de optie Van het web
3. kopieer de Wikipedia-URL uit de adresbalk van je internet-browser naar het tussenscherm in Excel

Wel jammer van de derde kolom: doordat er een jaar achter het aantal staat, ziet Excel dit aantal niet als getal.
Daar gaan we gauw iets aan doen; we gaan uit die kolom het aantal extraheren:

1. open zo nodig het scherm met het overzicht van de query’s in deze werkmap: de optie Query’s weergeven in de menutab Gegevens
2. dubbelklik op q_Inwoners (of klik rechts en kies de optie Bewerken)
3. klik ergens in de kolom Inwonertal
   
4. kies in de menutab Kolom toevoegen in het blok Uit tekst de optie Extraheren; kies Tekst voor scheidingsteken en vul het vervolgscherm in (tik op de (-toets, haakje openen)
5. verander het gegevenstype van de nieuwe kolom (klik op ABC en kies de optie Geheel getal) en wijzig de naam van de kolom via dubbelklikken in AantInwoners
6. verwijder de oude kolom Inwonertal en klik in de menutab Start op de button Sluiten en laden

Weer wat geleerd: ik wist niet dat Indonesië zo veel inwoners heeft!

NB Excel haalt alleen letters en cijfers op; je mist dus plaatjes (in dit geval de land-vlaggen) en bijvoorbeeld ook de opmerking bij het aantal van China.

Corona-cijfers

De gegevens in het vorige voorbeeld hadden we ook nog ‘handmatig’ kunnen overnemen; ze wijzigen hooguit één keer per jaar.

Een ander voorbeeld vinden we op de site news.google.com/covid19; de corona-data, die je daar kunt vinden, worden enkele keren per dag bijgewerkt. Een geautomatiseerd proces is dan een uitkomst.

Op het tabblad Corona van het Voorbeeldbestand staat het resultaat van een query:

Ook deze internet-pagina bevat een tabel, dus een koppeling via Power Query is snel gemaakt.
In de kolom Nieuwe gevallen is de tekst vervangen door de waarde 0 (nul).
Als laatste is nog een index toegevoegd; op die manier zien we dat Nederland het 21e land is, rekenend met het aantal Corona-gevallen.
De regel met Nederland wordt in Excel met behulp van Voorwaardelijke opmaak geaccentueerd.

Klik ergens rechts in de tabel en kies Vernieuwen.

Wil je weten welke stappen er zijn doorlopen om dit resultaat te bereiken: klik dubbel op de query q_Covid ( of klik rechts en kies Bewerken).

Je komt dan in de Power Query Editor. Klik op één van de stappen aan de rechterkant en boven in de formulebalk zie je wat deze stap daadwerkelijk inhoudt. De opdracht hierboven ziet er ingewikkeld uit, maar gelukkig is dit makkelijk te implementeren: klik rechts op een cel en kies de optie Waarden vervangen.

Maar zo eenvoudig als in de vorige 2 voorbeelden gaat het vaak niet; Excel kan alleen maar een tabel ophalen als in de internet-opmaak gebruik is gemaakt van de zogenaamde table-tag. Deze opmaak-techniek was ‘vroeger’ de standaard als je een overzicht op internet wilde plaatsen; met de opkomst van allerlei hulpprogramma’s om internet-pagina’s te genereren (bijvoorbeeld WordPress) worden steeds vaker alternatieve opmaak-opties gebruikt, die door Excel (nog) niet als tabel worden herkend.

Opmaak internet-pagina’s

Om de rest van het artikel beter te kunnen volgen moeten we het daarom (kort) over de opmaak van internet-pagina’s hebben.
Voor deze opmaak zijn diverse regels afgesproken zodat alle verschillende browsers weten hoe ze de informatie moeten interpreteren. Deze regels zijn vastgelegd in een taal: HTML (HyperText Markup Language). We kunnen hier geen uitgebreide uitleg daarvan geven, maar zullen de belangrijkste punten (voor ons doel) belichten.

Wanneer we rechts klikken op de Wikipedia-pagina van ons eerste voorbeeld, is één van de opties Paginabron weergeven.

Binnen HTML wordt gewerkt met zogenaamde tags; deze kun je herkennen aan de tekens < en >. Tags kun je zien als commando’s voor je internet-browser.
Als eerste krijgt de browser te zien dat het documenttype HTML is, daaronder dat binnen de html voor de site-inhoud de Nederlandse taal wordt gehanteerd. Class wordt onder andere gebruikt om elementen een bepaalde opmaak mee te geven.
Iedere internet-pagina moet een head hebben en ook een body (waar de echte inhoud van de pagina staat). Vaak wordt ook gebruik gemaakt van een footer.
De tag title wordt gebruikt om het tabblad in je browser een naam te geven.

Ieder blok van een internet-pagina wordt voorafgegaan door een tag en afgesloten met een sluit-tag (dezelfde naam maar met een /).

We kunnen deze paginastructuur nog op een andere manier bekijken. Wanneer je rechts klikt op het inwoneraantal van China, kies dan Inspecteren:

Je ziet dan ‘direct’ waar het element waar je op hebt geklikt, zich in de pagina-structuur bevindt. De head is dichtgeklapt (het pijltje wijst naar rechts en je ziet puntjes tussen de open- en sluit-tag), de body is geopend (pijltje naar beneden) dus daar zit het inwonertal in.
De body begint met twee div‘s, die direct weer worden gesloten; die zijn hier voor opmaak-doeleinden bedoeld.

NB div is de afkorting van division; hiermee kunnen makkelijk bepaalde secties van een pagina worden afgebakend.

Dan komen we in een div met als id de naam content. Daarbinnen komen we iets verderop in de div bodyContent.
Uiteindelijk zien we dan de tag table, met daarin een kop (thead) en een tbody. Binnen de tbody komt een regel-tag (tr) en daarbinnen 3 keer een td (data-cell).

NB misschien heb je geprobeerd om in het eerste voorbeeld bij het extraheren van het inwoneraantal als scheidingsteken het haakje-openen vooraf te laten gaan door een spatie. Hier kun je zien waarom dat niet werkte: tussen de html-spatie (&nbsp;) en het haakje staat nog een tag.

Artikelen van G-Info

Op iedere pagina van ginfo.nl staat rechts een overzicht van alle Tips & trucs. We gaan de gegevens uit die kolom overhevelen naar Excel (zie voor het resultaat het tabblad Artikelen in het Voorbeeldbestand en voor de uiteindelijke query q_G-Info-artikelen):

1. eerst gaan we die internet-pagina analyseren: klik in de browser rechts op de naam van het eerste artikel (in het voorbeeld Histogrammen) en kies de optie Inspecteren.

3. we zijn er bijna: We slaan een h3 over (daar zien we de kop van de kolom) en vinden dan binnen een ul (unordered list) diverse li-tags (list items). Dat zijn de artikelen, die we zoeken.
4. nu kunnen we aan de slag: kies in Excel in de menutab Gegevens in het blok Gegevensophalen en transponeren de optie Van het web
5. vul als URL in: www.ginfo.nl en klik OK
   
6. zoals we al hadden verwacht kan Power Query (PQ) geen tabel vinden, alleen de document-tag. Klik daarop en kies dan Bewerken.
7. 
   in de editor zien we maar 1 regel, het HTML-document. Maar in de kolom Children zitten alle blokken die we bij de analyse hebben gezien.
   Wanneer je die cel activeert (klik NAAST het woord Table), dan zie je beneden dat deze cel 2 elementen bevat, een HEAD en een BODY.
8. klik nu op het woord Table; in de editor zien we dan 2 regels:
   
   In de analyse hebben we gezien dat we in de body moeten zijn; klik op Table in die regel.
9. we gaan verder inzoomen zoals we hiervoor in de analyse hebben gezien: de eerste div, de eerste div na de header, dan de 2e div, de eerste div, nog een keer de eerste div en dan de aside.
   De h3 zouden we overslaan: kies de ul-table. En daar zijn ze!
   
10. klik NIET op een Table achter een LI: dan zoomen we in op één artikel en zien we de andere namen niet meer.
    We willen de gegevens van alle ‘children‘ hebben: klik op de button met de 2 pijltjes achter de kolomnaam
11. vul het tussenscherm als volgt in:
12. en nog een keer op dezelfde manier uitvouwen levert het volgende op:

NB heb je ergens een fout gemaakt? Klik aan de rechterkant Navigatie open (dubbelklikken of klikken op het ’tandwieltje’) en je kunt één of meerdere stappen terug gaan.

Nu we de gegevens hebben gevonden, moeten we nog wel wat opschoonacties doen (maar bedenk wel: dit hoef je binnen PQ allemaal maar één keer te doen, daarna kun je het overzicht met Vernieuwen iedere keer snel actualiseren):

1. verwijder alle kolommen, behalve de kolom waar de teksten in staan:
   
2. de lege regels verwijderen door rechts te klikken op één van de null-waarden:
   
3. de naam en de datum van het artikel moeten naast elkaar komen staan, we moeten dus rijen combineren. Dat kan bijvoorbeeld op de volgende manier (in het volgende artikel komt een andere methode aan bod):
   * kies in de menutab Kolom toevoegen de optie Indexkolom (de index moet met 0 beginnen)
   * direct gevolgd door , vul in het vervolgscherm de waarde 2 in; we willen 2 regels bij elkaar nemen
   
   NB Microsoft gebruikt hier een vreemde terminologie; wat bedoeld wordt is, dat we na deling een geheel getal overhouden (dus eventuele decimalen weg laten)
   * nu gaan we, met de laatst toegevoegde kolom geselecteerd, de rijen groeperen: kies in de menutab Startpagina de optie Groeperen op, kies als Bewerking de optie Som en als Kolom de kolom met teksten (we gaan teksten ‘optellen’)
   * dat kan natuurlijk niet! We moeten even wat corrigeren: wijzig in de formulebalk List.Sum([Text.2]) door Text.Combine([Text.2], “||”) en druk op Enter.
   LET OP PQ is hoofdletter-gevoelig.
   NB we hebben hier || als scheidingsteken gebruikt; we hebben iets nodig waar we van weten dat dit nergens in de tekst voorkomt.
   * selecteer de kolom met teksten, kies in de menutab Startpagina de optie Kolom splitsen en voer als aangepast scheidingsteken || in.
   PQ splitst de kolom én verandert het type van de nieuwe kolom automatisch naar datum.
   * nog een paar aanpassingen: verwijder de eerste kolom en wijzig de kolomkoppen.
   * kies Sluiten en laden

Weersverwachtingen

Nog een voorbeeld, nu van de website knmi.nl/nederland-nu/weer/verwachtingen.

Daarmee hebben we kolom gevonden met de informatie die we zoeken. Nu moeten er nog wat schoningsacties worden uitgevoerd en de lay-out aangepast vergelijkbaar met het vorige voorbeeld, inclusief het omzetten van rijen naar kolommen (zie de query q_KNMI in het Voorbeeldbestand).

Power Query vraagt daarbij naar een gewenst scheidingsteken: kies Aangepast en vul dan in Character.FromNumber(10). Standaard wordt deze scheiding als tekst ingevuld, dat is niet de bedoeling. Verwijder in de formulebalk de twee ” en druk op OK.

Nog een paar kleine aanpassingen en kies dan Sluiten en laden:

En zo hebben we de meest recente weersverwachtingen in Excel. Op ieder gewenst moment kunnen we dit overzicht actualiseren.

NB1 om de lay-out in Excel mooi te krijgen hebben alle cellen van het overzicht een opmaak meegekregen (via Ctrl-1 of rechtsklikken; zie het tabblad KNMI van het Voorbeeldbestand).

NB2 zoals al eerder aangegeven kunnen we helaas op deze manier niet de plaatjes overhalen.

NB3 op de site van het KNMI staat ook de datum en tijd aangegeven waarop het overzicht is bijgewerkt. Een mooie oefening om die ook terug te vinden!

---