Tagarchief: Webquery

Power Query en het Web 2



In het vorige artikel had ik aangegeven dat ik zou beschrijven hoe je gegevens die over meerdere internet-pagina’s zijn verspreid toch met Power Query in één keer kunt overhalen naar Excel.

Belofte maakt schuld ….

Basis

Allereerst een disclaimer: je kunt (natuurlijk) alleen maar gegevens van meerdere pagina’s combineren, wanneer die pagina’s qua structuur op elkaar lijken.

We nemen als voorbeeld de pagina Tips & trucs van G-Info.

Daar staan alle artikelen die in de loop van de jaren zijn gepubliceerd; tenminste het eerste gedeelte van die artikelen.
Om het overzichtelijk te houden vind je daar de artikelen in blokken van 5.
Kies je oudere artikelen dan zie je in de adresbalk van je browser: www.ginfo.nl/tips-trucs/page/2/, waarbij het paginanummer gaat oplopen.
NB gelukkig kunnen we ook op dezelfde manier de eerste pagina ophalen: www.ginfo.nl/tips-trucs/page/1/

We gaan de gegevens van de eerste pagina binnenhalen in Excel:

  1. Analyseer de internetpagina via de optie Inspecteren zoals in het vorige artikel uitgelegd.
  2. Kies in de menutab Gegevens in het blok Gegevensophalen en transponeren de optie Van het web en geef als URL in: ginfo.nl/tips-trucs/
  3. Klik in de Navigator op Document en kies Bewerken.
  4. Klik 2x op Table en dan op de Table achter Body.
  5. Dan 4x op Table achter de (eerste) DIV

  6. Nu we op het niveau van de artikelen zijn gekomen kiezen we Uitvouwen achter Children.
    LET OP zorg dat je bij het uitvouwen alleen de kolommen Children en Text meeneemt
  7. Kies nog 3x Uitvouwen met als resultaat:
  8. Selecteer de kolommen Text.4 en Text.3 en kies op de menutab Transformeren de optie Kolommen samenvoegen. Geef de nieuwe kolom de naam Artikel.
  9. Verwijder alle rijen, die we niet nodig hebben door er rechts op te klikken:
  10. Verwijder alle overbodige kolommen, geef de query de naam q_Tips1 en kies in de menutab Startpagina de optie Sluiten en laden.

Dat is al gelukt (zie het tabblad Tips1 van het voorbeeldbestand): we hebben de belangrijkste gegevens van de internetpagina in Excel.
Maar het zou mooier zijn als de kop van het artikel en de inhoud naast elkaar zou staan.

Van rijen naar kolommen

De methode die we hiervoor gaan gebruiken is een alternatief voor die uit het vorige artikel:

  1. Dubbelklik op de query q_Tips1.
  2. Kies in de menutab Kolom toevoegen de optie Indexkolom en direct daarna binnen Standaard de optie Delen (geheel getal) .
    Vul in het tussenscherm de waarde 2 in en geef de nieuwe kolom de naam Rij.
  3. Met de kolom Index geselecteerd, kies opnieuw Standaard, maar dan de optie Modulo; zorg dat door 2 gedeeld wordt.
    Geef deze kolom de naam Kolom.
    NB Met Modulo wordt de rest berekend van een deling.
  4. Verwijder de kolom Index.
  5. Selecteer de kolom Kolom, kies in de menutab Transformeren de optie Draaikolom en vul het vervolgscherm als volgt in:
  6. Verwijder de kolom met de naam Rij en wijzig de 2 overblijvende kolomnamen in Naam en Inhoud. Kies de optie Sluiten en laden (zie het tabblad Tips2 van het voorbeeldbestand).

NB pas wel even de cel-eigenschappen van de tabel aan: kies Terugloop bij de Tekstopties en de optie Boven bij Verticaal.

Query als functie gebruiken

De query, q_Tips2, die we hiervoor hebben gemaakt, willen we nu voor verschillende internetpagina’s van G-Info gebruiken. Hij moet zich dus als een functie gedragen, waar we door middel van een parameter kunnen aangeven, welke pagina moet worden opgehaald:

  1. Kies in de menutab Gegevens in het blok Gegevens ophalen de optie Power Query-editor starten.
  2. Selecteer aan de linkerkant de query q_Tips2 en kies de optie Geavanceerde editor in de menutab Startpagina.
  1. Kopieer alle stappen van deze query; kies Annuleren.
  2. Klik met de rechter muisknop in de linkerkolom en voeg een Lege query toe:
  3. Ga weer naar de Geavanceerde editor en plak de gekopieerde stappen uit punt 2.
  4. Voeg vooraan een regel toe:
    let AllePaginas=(PagNr) =>
  5. Wijzig de Bron-stap in:
    Bron = Web.Page(Web.Contents(“ginfo.nl/tips-trucs/page/”&Number.ToText(PagNr))),
  6. Voeg nog een laatste regel toe:
    in AllePaginas
  7. Klik op Gereed. Test de functie door als parameter een 1, 2 etc in te tikken en dan Aanroepen te kiezen.
  8. Geef de functie de naam fAllePags en kies de optie Sluiten en laden.

Functie gebruiken

De functie die we net gemaakt hebben gaan we gebruiken om met behulp van Power Query alle tips van G-Info tegelijkertijd op te halen:

  1. Allereerst moeten we aangeven welke paginanummers we willen importeren. We leggen dat in een Excel-tabel vast (met de naam tblPagNrs; zie het tabblad AlleTips van het voorbeeldbestand)
  1. Klik ergens in die tabel en kies in de menutab Gegevens in het blok Gegevens ophalen en transformeren de optie Van tabel/bereik.
  2. Kies in de menutab Startpagina de optie Kolom splitsen/Op scheidingsteken en vul het scherm als volgt in:

    NB we splitsen in 50 kolommen zodat bij uitbreiding van het aantal tips-pagina’s de routine goed blijft werken.
  3. Kies in de menutab Transformeren de optie Transponeren.
  4. Filter nu de lege rijen uit (rechtsklikken op een lege cel).
  5. Wijzig de kolomnaam in Pagina.
  6. Kies in de menutab Kolom toevoegen de optie Aangepaste functie aanroepen. Kies in het vervolgscherm bij Functiequery onze hiervoor gecreëerde functie; de rest van de opties zijn oké:

    LET OP de routine gaat nu de gegevens van alle pagina’s ophalen; dat kost even wat tijd!
  7. Vouw de kolom fAllePags uit.
  8. Verwijder de kolom Pagina.
  9. Geef de query de naam q_AlleTips en kies Sluiten en laden naar.

Helaas, we hebben bij het maken van de tips-query iets over het hoofd gezien: als er reacties zijn op een artikel dan ‘raakt het systeem in de war’ (zie het tabblad AlleTips van het voorbeeldbestand).

Ook staat er ergens nog zoiets als Een reactie tot dusver.
Bij het filteren van regels moeten we ook de reacties weglaten:

  1. Kies in de menutab Gegevens in het blok Gegevens ophalen de optie Power Query-editor starten.
  2. Klik rechts op de functie fAllePags en kies Dupliceren; wijzig de naam van de nieuwe functie in fAllePags2.
  3. Kies de optie Geavanceerde editor.
  4. Voeg als volgt na gefilterd4 2 stappen toe en wijzig de stap daarna:
    #”Rijen gefilterd4″ = Table.SelectRows(#”Rijen gefilterd3″, each [Name] <> “nav”),
    #”Rijen gefilterd5″ = Table.SelectRows(#”Rijen gefilterd4″, each not Text.Contains([Artikel], ” reactie tot dusver”)),
    #”Rijen gefilterd6″ = Table.SelectRows(#”Rijen gefilterd5″, each not Text.Contains([Artikel], ” reacties bekijken”)),

    #”Andere kolommen verwijderd” = Table.SelectColumns(#”Rijen gefilterd6“,{“Artikel”}),
  5. Klik op Gereed.
  6. Test de nieuwe functie door deze aan te roepen met als parameter bijvoorbeeld 3.
  7. Kies de optie Sluiten en laden.

NB Door de paginanummers op het tabblad AlleTips2 van het voorbeeldbestand te wijzigen en de tabel daaronder te Vernieuwen kun je het overzicht aanpassen aan je wensen.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd?


Power Query en het Web



Dit artikel gaat over het gebruik van Power Query als hulpmiddel om gegevens van het WEB te halen.

Hebben we eenmalig een overzicht nodig dan volstaat meestal een eenvoudige kopieer-plak-actie.

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

Op Wikipedia (nl.wikipedia.org/wiki/Lijst_van_landen_naar_inwonertal) is een mooi overzicht te vinden van het aantal inwoners per land. Dit wordt natuurlijk niet dagelijks bijgewerkt; op dit moment staan hier gegevens van 2020.

Maar hiermee kunnen we wel makkelijk de kracht van Power Query laten zien.

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
  1. in de Navigator zie je in dit geval dat we kunnen kiezen uit het hele document (de internet-pagina) of een tabel.
    Klik één keer op de naam Table 0 en je zult zien dat dit precies de gegevens zijn die we willen ophalen.
  2. kies rechtsonder het ‘vinkje’ naast Laden
  1. Kies daar Laden naar.
    In het vervolgscherm geven we aan dat we als resultaat een Tabel willen.
    Geef aan of deze op een bestaand werkblad moet komen of op een nieuwe.

Daar zijn de gegevens zoals we die op de internet-pagina hebben gezien (zie het tabblad Inwoners in het Voorbeeldbestand. Helaas wel zonder de mooie vlaggetjes!).
Zijn de gegevens op internet gewijzigd: klik rechts op de tabel en kies Vernieuwen.

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).

De derde staat in een gekleurde balk; klik op het pijltje en je ziet de inhoud van die tabel-cel.

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.
  1. het eerste wat opvalt is dat de table-tag nergens te vinden is.
    Binnen de body moeten we de eerste div hebben. Daarbinnen de eerste div na de header, daarbinnen de tweede div, en dan twee keer de eerste div en dan een blok aside.
  1. 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.
  2. nu kunnen we aan de slag: kies in Excel in de menutab Gegevens in het blok Gegevensophalen en transponeren de optie Van het web
  3. vul als URL in: www.ginfo.nl en klik OK
  4. zoals we al hadden verwacht kan Power Query (PQ) geen tabel vinden, alleen de document-tag. Klik daarop en kies dan Bewerken.

  5. 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.
  6. 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.
  7. 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!
  8. 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
  9. vul het tussenscherm als volgt in:
  10. 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.

Een korte analyse van deze site laat zien dat ook hier geen table-tag te vinden is. Dus dat wordt weer tellen!
NB de span-tag is ook een html-hulpmiddel om de pagina in secties te verdelen.
Met wat experimenteren moet je in PQ tot een overzicht van li-tags kunnen komen.

Vouw de kolom Children uit en daarna nog een keer.

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).

Om de dag en de datum in één kolom te krijgen hebben we de optie Kolommen samenvoegen in de menutab Transformeren gebruikt.

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!


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd?


Excel en het World-Wide-Web

Nee, dit is geen aankondiging van een nieuwe thriller, die ik geschreven zou hebben (dat laat ik aan een buurman over: zie glengoutstap.nl); in dit artikel wil ik laten zien welke mogelijkheden er zijn om Excel en het internet (www, world-wide-web) met elkaar te verbinden.

Het gaat dan wel om een eenzijdige verbintenis: hoe kunnen we met Excel op een makkelijke (?) manier informatie aan het internet onttrekken.

Methode 1

De meest gebruikte methode is:

  1. zoek op internet de pagina met gegevens, waar je iets mee wilt gaan doen in Excel
  2. selecteer met de muis de betreffende data
  3. kies Kopiëren
  4. ga naar Excel en kies Plakken

Voor eenmalige acties is hier niets mis mee; dit is dan zeker efficiënt en effectief. Maar wordt het een dagelijkse handeling om bijvoorbeeld koersgegevens op te halen, dan kan dat ook anders.

Methode 2

behr.nlVoor onderzoek naar koersverloop van indexen en bedrijven kom ik regelmatig op de site www.behr.nl; niet voor privé-gebruik ;-).
Daar kun je onder andere historische dagkoersen ophalen.
Even zoeken, maar via de URL www.behr.nl/Beurs/Slotkoersen/.a/aegon zijn de dagkoersen van Aegon te vinden.

Behr.nlBehr levert als resultaat een pagina met datums en de daarbij behorende koersen. Geen verdere poespas daar omheen; dus zeker ook geschikt voor een snelle kopieer- en plak-actie.

Maar heb je dit overzicht vaker nodig dan gaat we dat slimmer aanpakken:

  1. zorg in Excel, dat er een nieuwe werkmap geopend klaar staat (bijvoorbeeld door Ctrl-N te drukken)
  2. Webkies binnen de menutab Gegevens in het blok Ext. geg. ophalen (ofwel Externe gegevens ophalen) de optie Van web
  3. WebEr opent zich een nieuw scherm; op de adresregel moet de hierboven genoemde URL ingevoerd worden.
  4. klik op de button Ga naar
  5. de betreffende internet-pagina wordt opgehaald en onder de adresregel weergegeven. Alle blokken die op die pagina als een tabel zijn opgemaakt hebben een tekentje Web gekregen. Door daar op te klikken kan aangegeven worden of dat betreffende blok wel of niet moet worden opgehaald. In dit geval klikken we alleen op het 2e pijltje; dat wordt dan een groen vinkje.
  6. kies dan onderaan het webquery-scherm de optie Importeren
  7. nog even aangeven waar de gegevens moeten komen en klaar: de data staan nu in Excel om verder verwerkt te worden.
  8. sla het bestand op met de naam Aegon.xlsx.

Moet de lijst morgen (of volgende week, volgende maand) geupdate worden:

  1.  open het bestand Aegon.xlsx
  2. klik met de muis rechts op één van de gevulde cellen en kies de onderste optie Vernieuwen

In het Voorbeeldbestand staat op het tabblad Aegon deze web-query. Daar kan die dus ook bijgewerkt worden.

LET OP de koppeling tussen Excel en internet is gebaseerd op het gebruik van tabellen op web-pagina´s.
Door de implementatie van nieuwere technieken bij de ontwikkeling van web-pagina’s komt het steeds vaker voor, dat Excel geen gegevens of veel teveel gegevens gaat ophalen. Aandachtspuntje voor Bill Gates!

Gegevens ‘knippen’

Helaas zijn de data, die op deze manier bij Behr.nl worden opgehaald, niet direct bruikbaar:  op iedere rij staat de datum en de koers in dezelfde cel (gescheiden door een :-teken), de datum-notatie ‘ziet er niet uit’ en de decimalen in de koers worden vooraf gegaan door een punt ipv een komma.
In het tabblad Heineken in het Voorbeeldbestand zijn daarom 2 kolommen toegevoegd, die ons het omzetten uit handen nemen: in de eerste kolom wordt mbv de functie DATUM een echte datum gecreëerd:

=DATUM(“20″&LINKS(B2;2);DEEL(B2;3;2);DEEL(B2;5;2))

PS1 de 2 meest linkse posities van cel B2 geven het jaar weer, het gedeelte vanaf positie 3 en 2 lang (dus positie 3 en 4) vormt de maand en de posities 5 en 6 vormen de dag.
PS2 aangezien Behr geen eeuw-aanduiding gebruikt ‘plakken’ we zelf er 20 voor, anders gaat Excel met datums uit de 20e eeuw werken.

De koers bepalen we op de volgende manier:

=WAARDE(SUBSTITUEREN(RECHTS(B2;LENGTE(B2)-7);”.”;”,”))

We nemen van B2 het rechtse gedeelte (we laten 7 tekens weg: de datum en het :-teken) en substitueren de punt door een komma. Omdat Excel dan ‘denkt’, dat dit een tekst oplevert, moeten we de waarde daarvan nemen.

Gegevens opmaken

In het Voorbeeldbestand worden ook de koersen van KPN opgehaald (op het tabblad KPN, dus).
Deze pagina is verder opgemaakt met kaders etc. Zorg er voor, dat bij het Vernieuwen deze opmaak niet wordt overschreven: klik rechts op één van de cellen uit de web-query (dus niet de hulp-kolommen) en kies de optie Eigenschappen van gegevensbereik …
Zorg dat Celopmaak behouden staat aangevinkt.
Experimenteer met de diverse opties in dit keuzescherm.

AEX

Het verloop van de AEX-index (en de aandelen, die daar onderdeel van uitmaken) zijn te vinden op www.beurs.nl/koersen/aex/p1.

Helaas is dit ook zo’n site met veel ‘ballast’ rond de cijfers: zie het tabblad AEX in het Voorbeeldbestand. Pas op regel 139 komen de data, waar we naar op zoek zijn.

Nog enkele opmerkingen bij dit tabblad:

  1. na het invoeren van het adres in de web-query komt er een Script-foutmelding: op Ja klikken
  2. Webtijdens het bewerken van de Webquery is via de button Opties… de opmaak ingesteld op HTML-indeling. Een groot gedeelte van de internet-opmaak en -mogelijkheden blijft dan gehandhaafd.
  3. WebDaarnaast is via Eigenschappen van gegevensbereik … ingesteld dat iedere minuut de pagina wordt geactualiseerd.

NB1 hoewel de site beurs.nl de koersen echt real-time laat zien, is dat op deze manier niet het geval. Een vertraging van 20-30 minuten is normaal.

NB2 ’s avonds zullen de koersen niet meer veranderen; de AEX sluit om 17:30 uur (en gaat om 9 uur weer van start).

Opmaak AEX

WebIn het tabblad AEX2 is een mooier overzicht van de AEX-koersen te vinden.  Met behulp van de functie VERT.ZOEKEN worden de gegevens uit het tabblad AEX opgehaald (en nu maar hopen dat de lay-out van de site en dus van het tabblad AEX niet verandert!).
Door gebruik te maken van voorwaardelijke opmaak is snel te zien welke koersen veel of weinig zijn gestegen of gedaald ten opzichte van de slotkoers van de vorige dag (de grens, waarbij een regel groen of rood wordt, is in cel C2 aan te passen).

NB bij het Vert.Zoeken wordt gebruik gemaakt van de functie KOLOM. Deze retourneert het kolomnummer van de cel, die als parameter wordt meegegeven. Geef je geen parameter mee dan geeft de functie de kolom terug van de cel waar de functie in staat.
Op deze manier is het mogelijk om de formule in cel C5 door te kopiëren naar het hele gebied zonder dat er nog aanpassingen nodig zijn. Let ook op de notatie van de Zoekwaarde ($B5; de verwijzing is dus half absoluut en half relatief).

Wandelingen op Veldkruus.nl

In het Voorbeeldbestand wordt op het tabblad Veldkruus het totaal-overzicht van de wandel- en fiets-tochten van de website Veldkruus.nl opgehaald.
Duidelijk is te zien, dat ‘harde returns’ in teksten in Excel als nieuwe regels tevoorschijn komen; voor een snelle verwerking van de gegevens (bijvoorbeeld sorteren op lengte) wordt het er dan niet makkelijker op.

NB even reclame maken voor een andere hobby van mij: met enkele ex-collega’s proberen we de veldkruisen, kapelletjes etcetera in Zuid-Limburg in kaart te brengen. Om het nog aantrekkelijker te maken zetten we ook ‘kruis-tochten’ langs deze objecten uit: zie Veldkruus.nl.

Dynamische web-query’s

Een groot nadeel van de hiervoor geschilderde koppeling tussen Excel en het Web is, dat de query een statisch karakter heeft: heb je een mooi overzicht van Heineken gemaakt en je wilt dezelfde gegevens van Starbucks zien, dan moet je alles opnieuw uitvoeren.

Gelukkig kunnen we de query met wat kunstgrepen dynamischer maken. Aan de hand van de slotkoersen van Behr.nl gaan we dit uitwerken. Het begin is hetzelfde als hierboven al aangegeven:

  1. zorg in Excel, dat er een nieuwe werkmap geopend klaar staat (bijvoorbeeld door Ctrl-N te drukken)
  2. kies binnen de menutab Gegevens in het blok Ext. geg. ophalen de optie Van web
  3. in het ‘Nieuwe web-query’-scherm voeren we op de adresregel de URL www.behr.nl/Beurs/Slotkoersen/.a/aegon in.
  4. klik op de button Ga naar
  5. de betreffende internet-pagina wordt opgehaald en onder de adresregel weergegeven. Alle blokken die op die pagina als een tabel zijn opgemaakt hebben een tekentje Web gekregen. In dit geval alleen op het 2e pijltje klikken.

In plaats van importeren, slaan we deze query op. Rechts boven (naast Opties…) zit daarvoor een button Web9. Sla de query op met de naam Slotkoersen.iqy , bijvoorbeeld op het Bureaublad. Kies daarna Annuleren.

Web10Nu gaan we dat bestand, Slotkoersen.iqy, openen met een simpele tekstverwerker (in het voorbeeld met Kladblok; in de Verkenner rechtsklikken op het bestand).
Iedere web-query moet met de eerste 2 regels beginnen; de 3e regel herkennen we natuurlijk direct: dat is onze URL.
Dan een lege regel; gevolgd door de mogelijke instellingen voor web-query’s.

We passen de derde regel aan: op de plaats waar nu hard staat wat gezocht moet gaan worden (in dit geval ‘.a/aegon’) komt een parameter Fonds:

http://www.behr.nl/Beurs/Slotkoersen/[“Fonds”, “Geef codering van fonds (incl. letterindeling zoals ‘.k/kpn’)”]

LET OP de plaats van de [‘s, aanhalingstekens en de komma zijn wezenlijk

Sluit Kladblok en sla de query onder dezelfde naam op en dubbelklik op de bestandsnaam.
Excel zal opstarten en vraagt naar een code voor het fonds (zoals we hiervoor in de iqy bij de parameter hebben ingevoerd) en laat daarna de betreffende gegevens zien.
Klik rechts op een cel en kies Vernieuwen: dezelfde vraag komt weer en nu heb je de mogelijkheid om een ander fonds op te halen.
Zie ook het tabblad Behr1 in het Voorbeeldbestand.

Web11Het kan nog mooier:

  1. dubbelklik op Slotkoersen.iqy
  2. geef een code op: bijvoorbeeld .h/heineken
  3. tik dan in cel B1 in: .a/aegon
  4. rechtsklik op een cel van de web-query en kies Vernieuwen
  5. in plaats van een code tikken we nu in =b1 en zetten de 2 vinkjes aan
  6. tik in cel B1 een andere code: .s/Starbucks en de gegevens worden automatisch opgehaald!

In het tabblad Behr2 van het Voorbeeldbestand is dit nog iets verder uitgewerkt met behulp van Gegevens-validatie.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: