Maandelijks archief: juli 2014

Doel zoeken in de Tour de France

Onze vakantie-bestemming is toch Frankrijk geworden: we zijn in de Provence terecht gekomen.

Gordes

Nog mooier: we hebben vorige week de Tour de France live meegemaakt.
In de etappe naar Nîmes zou de karavaan ook langs Gordes gaan (75 km voor de finish). Dat leek ons een mooie gelegenheid om 2 leuke zaken te combineren: een bezoek aan het schilderachtige, pittoreske kunstenaarsdorpje en kijken of we ‘onze jongens’ nog aan het werk zouden kunnen zien (en misschien nog iets leuks vangen bij de reclamekaravaan!).

Na enkele uren in de brandende zon (gelukkig had ik een petje van de Carrefour kunnen bemachtigen) kwamen daar de 2 koplopers aan: de Zwitserse kampioen Elmiger en de Amerikaan Bauer. Zo te zien was het scherpe bij die jongens er ondertussen vanaf. Ik schat hun snelheid op ongeveer 40 km/uur.

Hoofdrekenen

Daarna was het 6 minuten lang stil tot we in de verte het peloton aan zagen komen.
Ik vroeg me af hoe hard ze eigenlijk zouden moeten fietsen om voor de finish in Nîmes de koplopers in te halen: 6 minuten voorsprong, dat is 1/10 uur, dus met 40 per uur waren de 2 koplopers al 4 km verder. Dus voor hen resteerden dan nog 71 km. Met een gemiddelde snelheid van 40 km/uur zou dat iets minder dan 1,8 uur zijn (pakweg 106 minuten). In diezelfde tijd zou het peloton dan nog 75 km moeten gaan oftewel een dikke 42 km/uur.

Hoofdrekenen TourPoeh, hoofdrekenen in de vakantie, in de volle zon, viel nog niet mee! En wat nog erger was: in die tijd was het peloton, inclusief lange stoet volgwagens al weer om de volgende bocht verdwenen.

Dus ik dacht, daar moet ik toch maar een Excel-sheet aan wijden (en zorgen dat ik, bij een volgend bezoek aan de Tour, Excel bij me heb!). De berekening is redelijk recht toe recht aan (zoals hierboven uitgelegd). Maar dit is ook wel een mooie gelegenheid om eens een keer het onderdeel Doelzoeken van Excel toe te lichten.

Doelzoeken

In het voorbeeldbestand heb ik een overzicht gemaakt na hoeveel tijd de diverse rijders Tour2(kopgroep of peloton) bij km 75, 70, 65 etc zijn afhankelijk van hun snelheid:
bij 75 km is de tijd voor het peloton 0 en voor de kopgroep -6 min, bij 70 km is dat respectievelijk 7,5 en 1,5 minuut (er even van uitgaande dat iedereen 40 km/uur blijft fietsen).
Onder deze veronderstellingen is de kopgroep dan na 106,5 minuten in Nîmes en het peloton in 112,5. Nog steeds een verschil van 6 minuten.

Door de gemiddelde snelheid van het peloton aan te passen (in cel C8) kun je zien wat het effect op het verschil bij de finish is. Door de snelheid zodanig te kiezen, dat het verschil 0 is, weet je hoe hard het peloton gemiddeld moet rijden om er in ieder geval nog een sprintersetappe van te maken.

Die laatste stap (moet het 42 zijn? Nee iets meer. 42,2? Nog iets meer) kunnen we Excel laten doen via Doelzoeken:

  1. kies in de menubalk Gegevens
  2. bij de Hulpmiddelen voor Gegevens, de optie Wat-als-analyse
  3. en dan de optie Doelzoeken
  4. bij Cel instellen moet de cel met de waarde voor het verschil op de finish komen: dus cel F28
  5. die cel moet 0 worden: dus Op waarde wordt 0
  6. Door wijzigen van cel moet zijn C8 (snelheid van peloton)
  7. klik OK en nogmaals OK
  8. en ook Excel komt met 42,25 km/uur (iets vlugger dan mijn hoofdrekenen!)

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

Zomer-links

Nu de spanning van het WK-voetbal weg is, kunnen we ons weer focussen op andere zaken.

Tour de FranceDe Tour de France is ondertussen al weer 10 dagen onderweg; ook daar val je van de ene verrassing in de andere.

Ben benieuwd of de Nederlanders nog meer verrassingen voor ons in petto hebben (dank, Lars!)

Misschien dat ik ze de komende weken nog ergens ga tegenkomen: ik ga een paar weken vakantie vieren (maar of het Frankrijk gaat worden?).

Voor degene, die deze zomer door moeten werken, heb ik een paar links naar websites:

  1. DE site, waar altijd wel iets interessants te vinden is: chandoo.org
  2. worksheet.nl
  3. exhelp.be

ChandooZeker de website van Chandoo is de moeite waard. Ik heb daar menig goed idee opgedaan!


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

GPX? XML! dus Excel

StravaVorige week heb ik een fietstocht georganiseerd voor een groep collega’s; mooi weer, wat heuvels, prachtige omgeving, gezellige mensen: wat wil een mens nog meer?
Nou, wat minder discussie over het aantal gefietste kilometers en de geklommen hoogtemeters zou prettig zijn!

Zeker nu steeds meer fietsers gebruik maken van een GPS (al dan niet op de smartphone) en de kilometers op Strava worden gezet, eindigt een fietstocht vaak in een langdurige twist over de exacte cijfers van de geleverde prestatie.
Trouwens niet alleen bij het fietsen gebeurt dit; ik ken een fanatieke groep wandelaars waar na een tocht flink wordt overlegd welk aantal kilometers aan het thuisfront kan worden gemeld (het hoogste natuurlijk!).

GPX

Om te onderzoeken waar de verschillen door worden veroorzaakt, kwam ik er al snel achter, dat dit inherent is aan het gebruik van een GPS. De verbinding met de satellieten is niet altijd optimaal, waardoor verstoringen kunnen optreden. Zeker wanneer het gaat over het meten van de hoogte, laat menige GPS nog wel eens een steek vallen. Of ineens is de ontvangst beter en geeft de GPS een stijging (of daling) aan terwijl je stil staat!

Om de resultaten beter te kunnen vergelijken heb ik de tocht, die ik op Strava had gezet, geëxporteerd naar een GPX-bestand. Dit type bestand wordt bij navigatie-software meestal als standaard voor uitwisseling gebruikt. Soms is een dergelijk bestand direct van de GPS of smartphone te downloaden; anders kun je het omzetten met behulp van een programma als bijvoorbeeld Basecamp.

Nader onderzoek (met het alom gewaardeerde hulpmiddel Kladblok) leverde op, dat het eigenlijk een XML-bestand is.

XML

XML begint de Haarlemmermeerolie voor bestandsuitwisseling te worden. Deze standaard wordt door steeds meer pakketten ondersteund, waaronder zeker ook al jaren door Excel!

Waarom ik als titel voor deze blog “GPX? XML! dus Excel” heb gebruikt mag nu duidelijk zijn.

GPX importeren in Excel

Het voert te ver om hier alles rond XML uit de doeken te doen, dus ik geef een verkorte handleiding hoe de GPX-file in te lezen in Excel:

  1. Open Excel, zorg dat er geen bestand geopend is
  2. download een GPX-bestand (van Strava, GPS, internet)
  3. sleep het GPX-bestand naar het Excel-symbool op de werkbalk, Excel klapt dan open, sleep het GPX-bestand nu in Excel (de cursor krijgt dan een plusje) en laat de muis-button los
  4. Excel geeft een foutmelding, maar die negeren we gewoon, dus op JXMLa klikken
  5. in het volgende dialoogscherm kies je de eerste optie (Als een XML-tabel)
  6. 2 keer waarschuwingen negeren door OK te klikken en daar is onze route!

XML2

Excel maakt er direct een zogenaamde tabel van; in latere verwijzingen zie je de naam Tabel2 terugkomen. Binnenkort toch eens wat vertellen over de (nieuwe) mogelijkheden van tabellen!

Niet alle kolommen zijn voor ons interessant, maar E en F leveren de coördinaten (lat=latitude/breedtegraad, lon=longitude/lengtegraad), G de hoogte (ele=elevation), H een samengepakte vorm van de dag en het bijbehorende tijdstip en I de temperatuur op dat moment.

GPX-analyse

We gaan dit bestandje nu verder analyseren: download het Voorbeeldbestand.

In het eerste werkblad staan de gegevens van Strava. Ik heb een tweede werkblad aangemaakt (Berek), waarin we diverse berekende kolommen zien:

  1. in kolom A ‘knippen’ we het tijdstipXML3 los van de datum door een DEEL er van te pakken. Aangezien dit dan een tekst is, maken we er via TIJDWAARDE een echte tijd van.
  2. in B3 staat de formule
    =AFRONDEN(6370973,27862*((2*BOOGSIN(WORTEL((SIN((RADIALEN(Strava!E2)-RADIALEN(Strava!E3))/2)^2)+COS(RADIALEN(Strava!E2))*COS(RADIALEN(Strava!E3))*(SIN((RADIALEN(Strava!F2)-RADIALEN(Strava!F3))/2)^2)))));0)
    Het voert te ver om deze door te spitten, maar uit de coördinaten van het tijdstip uit regel 3 en die van de vorige regel, wordt de afstand daartussen bepaald (in meters).
  3. in C gaan we die meters gecumuleerd volgen (als kilometers)
  4. D bevat de verstreken tijd tussen twee metingen en E de gecumuleerde tijd
  5. en in F staat de snelheid: B gedeeld door D.
    Wel even opletten: we willen de snelheid in km/uur, dus de getallen in B moeten gedeeld worden door 1.000; aangezien in Excel een tijd intern als fractie van een dag wordt vastgelegd (dus 6 uur wordt in Excel 0,25 etc) vermenigvuldigen we de getallen in D nog met 24 om er uren van te maken.

Bestudering van de resultaten verklaart al wat van de vreemde snelheidsgrafieken in Strava en andere software: tussen veel metingen zitten soms maar enkele seconden en slechts luttele meters (toch eens harder gaan fietsen!). Aangezien we hier dan met relatief grote afrondingen zitten, kan het resultaat (de snelheid) ook behoorlijk variëren.

Om dit effect te verkleinen heb ik nog wat kolommen toegevoegd, waarin ik de snelheid niet tussen 2 opeenvolgende metingen bepaal, maar de intervallen vergroot. Hierdoor wordt het afrondingsprobleem voor een gedeelte uitgemiddeld.

Google Maps

Om makkelijk te kunnen analyseren waarom de snelheid op sommige stukken zo hoog (of laag) is heb ik in kolom K nog een hyperlink naar Google-maps toegevoegd:
=HYPERLINK(“http://maps.google.nl/maps?q=”&SUBSTITUEREN(Tabel2[@lat];”,”;”.”)&”,”&SUBSTITUEREN(Tabel2[@lon];”,”;”.”))

Meestal zoek je in Google-maps via een plaatsnaam en/of adres, maar je kunt ook coördinaten opgeven.
Aangezien Google een punt als decimaal scheidingsteken verwacht (de 2 coördinaten worden door een komma gescheiden), voeren we op de lat en lon een substitutie uit. Het resultaat kan er dan als volgend uitzien:
http://maps.google.nl/maps?q=50.867841,5.99487

Grafieken

XML-grafiekOmdat zoveel detail-informatie zoals weergegeven in het werkblad Berek zich uitstekend leent om grafisch weer te geven, heb ik een werkblad Grafieken toegevoegd.

De bovenste grafiek komt uit Strava; in de tweede is duidelijk te zien, dat de berekende snelheid (met een interval van 4 metingen) heel goed aansluit bij Strava!

In de derde grafiek zijn de hoogte en de snelheid niet tegen de afstand maar tegen de tijd uitgezet. XML5
Dan valt direct een vreemd stuk op in de grafiek. Bij de afstand-grafiek liep de lijn door; bij de tijd zit een onderbreking??

Hier is goed te zien hoe slecht de (mijn?) GPS met hoogtes omgaat: na de pauze waren we echt niet gestegen (alleen koffie gedronken!).

LET OP: bij het maken van dergelijke grafieken (waar de gegevens voor de x-as niet gelijkmatig zijn verdeeld; de ene keer 1 sec, dan weer 5 sec), kun je geen gewone Lijn-grafiek nemen; kies dan Spreiding als grafiektype.


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