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

Voor 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 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. kies binnen de menutab Gegevens in het blok Ext. geg. ophalen (ofwel Externe gegevens ophalen) de optie Van web
3. Er 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  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. tijdens 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. Daarnaast 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

In 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  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 . Sla de query op met de naam Slotkoersen.iqy , bijvoorbeeld op het Bureaublad. Kies daarna Annuleren.

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

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

---