Google Maps direkt in MS Excel anzapfen

01. Feb 2019 | Blog

VON Florian Nachbagauer

Trotz des Zeitalters von BigData ist es nicht absehbar, dass MS Excel von unseren Büro- und Privatcomputern verschwindet. Es bietet doch unzählige Möglichkeiten, kleinere Datenmengen aufzubewahren, anschaulich aufzubereiten und diverse Berechnungen durchzuführen. Deshalb ist es auch interessant, direkt von MS Excel aus verschiedene Datenquellen anzuzapfen. Für automatisierte Abfragen stellt Google (und auch viele andere Anbieter von Daten) eine sogenannte Web-API, also Programmierschnittstelle, zur Verfügung, welche mittels VBA auch in Excel abgefragt werden kann.

 

Angenommen: Sie müssen eine Kilometerabrechnung machen. Sie sind das letzte Jahr dienstlich sehr viel herumgefahren, haben aber nicht jedes Mal den Stand Ihres Kilometerzählers notiert. Sie haben eine lange Liste von Orten, an denen Sie waren und bräuchten nun die Auto-Distanz dazwischen. Sie könnten jetzt jede einzelne Strecke bei Google Maps händisch abfragen und dann in eine Excel-Datei kopieren. Allerdings haben wir angenommen, dass Sie im letzten Jahr derart viele Strecken hatten, dass diese Arbeit viel zu mühsam ist. Viel einfacher wäre es doch, wenn sie einfach eine Excel-Formel aufrufen, die Ihre Strecken automatisch abfragt und einfügt. Glücklicherweise ist das möglich. Sie müssen nur eine VBA-Funktion definieren, die genau das macht. Und was wäre das für ein Blogbeitrag, wenn Sie den zugehörigen jetzt selber schreiben müssten, denn der folgende Code macht genau das:

 

'Calculate Google Maps distance between two addresses
Public Function GetDistance(start As String, dest As String)
    Dim firstVal As String, secondVal As String, lastVal As String
    firstVal = "
https://maps.googleapis.com/maps/api/distancematrix/json?origins="
    secondVal = "&destinations="
    lastVal = "&mode=car&language=en&sensor=false&key=YOUR_KEY"
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    Url = firstVal & Replace(start, " ", "+") & secondVal & Replace(dest, " ", "+") & lastVal
    objHTTP.Open "GET", Url, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ("")
    If InStr(objHTTP.responseText, """distance"" : {") = 0 Then GoTo ErrorHandl
    Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """value"".*?([0-9]+)": regex.Global = False
    Set matches = regex.Execute(objHTTP.responseText)
    tmpVal = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
    GetDistance = CDbl(tmpVal)
    Exit Function
ErrorHandl:
    GetDistance = -1
End Function


Damit dieser Code funktioniert, müssen Sie YOUR_KEY durch einen gültigen Key für die Google API ersetzen. Um einen Key zu bekommen, muss man bei der Google Maps Plattform einen Account eröffnen. Überschreitet man mit der Anzahl der Abfragen ein gewisses monatliches Kontingent nicht, ist es gratis. Das genaue Kontingent ändert sich manchmal, kann aber aktuell unter dem Link nachgelesen werden.

 

Ein anderer Anwendungsfall wäre zum Beispiel, wenn man zu einer Reihe von Adressen die Geokoordinaten braucht. Auch hierfür gibt es eine API. Und falls Sie bereits dabei sind, die Excel-Welt zu verlassen, um sich DataScience Tools wie R zuzuwenden: In R sind derartige API-Anfragen in der Regel noch einfacher, weil es die zugehörige Funktion meistens schon gibt und Sie sie nicht selbst zusammenschustern müssen.

 

Eine genauere Anleitung, wie der obige VBA-Code zum Laufen gebraucht werden kann, findet sich hier.

Hier können Sie den Verfasser gerne kontaktieren: florian.nachbagauer@grawe.at

Risikohinweis

HINWEIS: Die Security BLOGS stellen lediglich die persönliche Meinung des Verfassers im Erstellungszeitpunkt und daher nicht die Meinung des Medieninhabers dar. Eine Haftung für diese Aussagen kann vom Medieninhaber ausdrücklich nicht übernommen werden. Die Wertentwicklung der Vergangenheit lässt keine verlässlichen Rückschlüsse auf die zukünftige Entwicklung eines Investmentfonds zu. Ausgabe- und Rücknahmespesen der Fonds sowie sonstige externe Spesen und Steuern sind in den Performanceberechnungen nicht berücksichtigt und mindern die Performance. Ertragserwartungen stellen bloße Schätzungen zum Zeitpunkt der Erstellung der Informationen dar und sind kein verlässlicher Indikator für eine tatsächliche künftige Entwicklung. Die aktuellen Prospekte und die Wesentlichen Anlegerinformationen ("KID", "KIID") sind in deutscher Sprache auf der Homepage www.securitykag.at (Fonds) sowie am Sitz der Security Kapitalanlage AG, Burgring 16, 8010 Graz als Emittentin und der Liechtensteinischen Landesbank (Österreich) AG, Heßgasse 1, 1010 Wien, als Depotbank kostenlos erhältlich. Beachten Sie bitte auch die weitergehenden Risikohinweise in den Verkaufsprospekten und unter www.securitykag.at/fonds/risikohinweis/ sowie die Offenlegung im Sinne des § 25 Mediengesetz und www.securitykag.at/fusszeile/impressum-offenlegung/