Aapeli Vuorinen

Hi, I'm Aapeli! Two defining features of my character are: a deep curiosity for understanding what drives the world forward; and a need to always be solving new, tough problems. I'm particularly interested in using advanced technology and mathematical modelling to come up with creative solutions and to make awesome products. I find that the projects I work on increasingly rely on data, so I've lately been concentrating a lot on machine learning, statistics, and other data related areas. By formal training I'm a mathematician and statistician, but I've always had a passion for software engineering. Feel free to get in touch if you like what you see on this site. I'm always open to exploring new things with other creative, smart people.

Around 3 minutes (492 words). Published 2017-03-03.

Apartment hunting with Google Sheets and the Google Maps Distance Matrix API

I just recently moved to Melbourne, Australia to complete a Master in Science in Statistics and Mathematics, hopefully involving a bunch of Machine Learning, at the University of Melbourne.

However, moving out from my parents’ place, I now had to hunt for an apartment to live in, which is no trivial task with a low budget during peak season in Melbourne! In addition to this, I was sick of commuting for over ten hours a week on expensive buses, so I had to take this into consideration. I was also doing this with potential roommates and needed to work concurrently with them, collaborate, and share information.

Google Sheets

Screenshot of section from Google Sheets

To aid myself, and all of us, I hacked together (at two in the morning, of course), a quick Google Sheets spreadsheet that automatically scrapes apartment information from realestate.com.au. I do this with the beautiful importxml(url, xpath) function in Google Sheets. You give it the URL to scrape, as well as the XPath of the element, and it gets the content of the given element. I’m pretty certain it does these requests on my computer with JavaScript, or something.

Using this, the spreadsheet automatically gets the address, weekly price, availability date, bond value, as well as the number of each type of room (bed, bath, carpark) of the apartment. It then assigns penalties and values to each of these variables (an extra bath might be worth an extra $100 to me), and computes the “weighted weekly price” of the apartment.

Google Maps Distance Matrix API

Screenshot of response from Google Maps Distance Matrix API

I didn’t stop there! In addition to just including these variables, I also connected to the Google Maps API, where I set up a free tier API key to connect to the Distance Matrix API. I set an hour of commuting time to be worth approximately $25. I really do hate wasting time on public transport. I then used the importxml function along with the xml output option to get the walking time from Google Maps with the following formula:

=importxml("https://maps.googleapis.com/maps/api/distancematrix/xml?units=metric&mode=walking&origins=<starting address>&destinations=<ending address>&departure_time=<travel time>&key=<API key>","//distance/value")/60

My actual queries looked like the following:

=importxml("https://maps.googleapis.com/maps/api/distancematrix/xml?units=metric&mode=walking&origins="&$AJ12&"&destinations="&cbdAddress&"&departure_time="&travelTime&"&key="&apiKey,"//distance/value")/60

Here I first instruct the API to send back xml that I can parse with importxml, then to use metric units, and walking (instead of driving). I set the travel time to be 1490965200, which corresponds to the number of seconds between midnight 01/04/17 AEDT and the Unix epoch (1970-01-01 00:00:00 UTC). I set it to midnight as I tend to rampantly jaywalk and traffic does not really influence my walking times. I then extracted the walking seconds between the two addresses using the XPath //distance/value. Finally, I divided this by 60 to get the result in minutes. To add these to the computation, I computed several distances, and added the cost of walking to the total weekly cost, using about 10 trips to my university every week, as well as 2 trips downtown.