In een vorig artikel hebben we aandelenkoersen opgehaald vanaf internet. We kregen toen het volgende resultaat:
De koersen staan nu in Excel, maar eerlijk gezegd kunnen we er nog niet zoveel mee. De datum en de tijd staan in één kolom, gescheiden door een dubbele punt en een spatie. Verder staat er op rij 117 nog een uitzondering:
090409: 9.55X
Gelukkig kunnen we met formules ervoor zorgen dat de datum en de koers, ieder in het juiste formaat in een eigen kolom komen te staan.
Laten we beginnen met de datum. Als we de gegevens bekijken dan is datum altijd 6 karakters lang in het formaat jjmmdd. We kunnen hier een datum van maken met de functie DATUM(jaar;maand;dag). Met deze functie kan Excel op basis van drie getallen een datum samenstellen.
Het eerste getal dat we nodig hebben is het jaar. Dit zijn de eerste twee tekens in onze kolom. Met de functie LINKS(tekst;[aantal tekens]) kunnen we een willekeurig aantal tekens ophalen aan het begin van reeks tekens. In ons geval moeten we invullen in (er van uitgaande dat de tekst in cel A4 staat):
LINKS(A4;2)
Hiermee zeggen we dat we de eerste twee tekens van cel A4 willen hebben. Helaas zijn we er nu nog niet. Excel ziet cel A4 als tekst terwijl DATUM() een getal nodig heeft. We moeten het resultaat van onze formule converteren naar een getal. Dit kan met de functie WAARDE(tekst). We krijgen dan:
WAARDE(LINKS(A4;2))
Het resultaat is dan 9. Als we dit getal doorgeven aan DATUM() dan denkt Excel dat het gaat om 1909. Daarom moeten we er eerst 2000 bij optellen:
WAARDE(LINKS(A4;2))+2000
Hiermee hebben we het jaar voor elkaar. Nu de maand. Deze bevindt zich op positie 3 en 4 van onze tekenreeks in cel A4. Aan LINKS() hebben we nu niks want de eerste twee tekens moet Excel overslaan. Gelukkig kunnen we dit oplossen met de functie DEEL(tekst;begin_getal;aantal-tekens). Het begin_getal moet dan 3 zijn en we willen twee tekens ophalen. Echter ook deze functie moeten we weer binnen een WAARDE() functie zetten om er een getal van te maken.
WAARDE(DEEL(A4;3;2))
De dag kunnen we op dezelfde manier ophalen. We beginnen alleen nu niet op positie 3, maar op positie 5
WAARDE(DEEL(A4;5;2))
De complete datum kunnen we nu ophalen door de drie functies voor het ophalen van jaar, maand en dag te combineren met de DATUM(jaar; maand; dag) functie:
DATUM(WAARDE(LINKS(A4;2))+2000;WAARDE(DEEL(A4;3;2));WAARDE(DEEL(A4;5;2)))
Met deze formule kunnen we in kolom E de datum weergeven. Nu de koers nog. Laten we nog even naar de regel met de uitzondering kijken:
Hier zitten een paar zaken mee en een paar zaken tegen. Wat mee zit is dat de koers altijd begint op positie 9 (1e 6 posities zijn de datum, positie 7 een ‘:’ en positie 8 een spatie. Wat tegenzit is dat het decimale scheidingsteken een punt is, terwijl we een komma willen hebben en dat de koers kennelijk soms afgesloten wordt met een ‘X’. Stel dat er niks tegenzat dan zouden we weer een combinatie van WAARDE() en DEEL() kunnen gebruiken:
WAARDE(DEEL(A4;9;10))
De keuze voor het ophalen van 10 tekens is hierbij willekeurig. Belangrijk is dat je genoeg tekens ophaalt om de hele koers op te halen. We moeten deze formule nu zodanig aanpassen dat de punt wordt vervangen door een komma. Dit kan met de functie SUBSTITUEREN(tekst; oud_tekst; nieuw_tekst;[rang_getal]). Tekst wordt in dit geval DEEL(A4;9;10). Oud_tekst is de punt (het teken dat we willen vervangen) en nieuw_tekst een komma (het teken waarmee we de punt willen vervangen). [rang_getal] is optioneel en hebben we in dit geval niet nodig. De formule komt er als volgt uit te zien:
SUBSTITUEREN(DEEL(A4;9;10);".";",")
Op dezelfde manier kunnen we de X vervangen door een spatie. De bovenstaande formule vullen we in voor ‘tekst’, de ‘X’ voor ‘oud_tekst’ en een spatie voor ‘nieuw_tekst’. Dat ziet er zo uit:
SUBSTITUEREN(SUBSTITUEREN(DEEL(A4;9;10);".";",");"X";" ")
Voor Excel is het resultaat van deze formule nog steeds een tekst, terwijl wij een getal nodig hebben. Daarom moeten we de hele formule nog een keer in de WAARDE() functie zetten:
WAARDE(SUBSTITUEREN(SUBSTITUEREN(DEEL(A4;9;10);".";",");"X";" "))
We hebben nu twee formules, één voor de datum en één voor de koers:
DATUM(WAARDE(LINKS(A4;2))+2000;WAARDE(DEEL(A4;3;2));WAARDE(DEEL(A4;5;2)))
WAARDE(SUBSTITUEREN(SUBSTITUEREN(DEEL(A4;9;10);".";",");"X";" "))
Zetten we de formule voor de datum in cel B4 en die voor koers in C4 en verversen we vervolgens de gegevens van de webquery, dan zul je zien dat Excel automatisch de formule naar beneden kopieert (mits we dit hadden aangevinkt bij het aanmaken van de query, zie het vorige artikel). Vervolgens kunnen we met de datum en koers nu doen wat we willen.