Using Google timeline to work out EV suitability.

quietsailor

I've been following this forum for a while with great interest as I would like to move towards BEVs or a hybrid. I had planned to carry out research to prove that an electric car is suitable for my lifestyle by logging my trips for a year but my present car is backing me into a corner, it's decided that it no longer likes the clutch that it has....and while it is at it a few suspension components as well. Seeing as it is an Alfa 1.8 petrol estate it's going to be expensive,

My brother suggested downloading my google timeline info and working out my driving from that but it's not any way feasible to open each day over the last year and write the places/distances into an excel sheet.

Downloading the year gives me a massive file with a .JSON extension and I can't find a way to read it -- I think a lot of people on here would be logging their trips and I'm hoping someone has cracked a way that a non-IT person can follow. If you have can you share it with me please?

gally74

quietsailor wrote: »

I've been following this forum for a while with great interest as I would like to move towards BEVs or a hybrid. I had planned to carry out research to prove that an electric car is suitable for my lifestyle by logging my trips for a year but my present car is backing me into a corner, it's decided that it no longer likes the clutch that it has....and while it is at it a few suspension components as well. Seeing as it is an Alfa 1.8 petrol estate it's going to be expensive,

My brother suggested downloading my google timeline info and working out my driving from that but it's not any way feasible to open each day over the last year and write the places/distances into an excel sheet.

Downloading the year gives me a massive file with a .JSON extension and I can't find a way to read it -- I think a lot of people on here would be logging their trips and I'm hoping someone has cracked a way that a non-IT person can follow. If you have can you share it with me please?

Why not start a company. Sounds like a great app opportunity there.

pwurple

The easiest eay to do it is to use the graphical interface... read the map.

So, when you go to google timeline, and are logged in with the google account you use on your phone, set the year to 2016 say... leave month and day blank. wait a few seconds, and all your destinations will appear.

You know where you live, so take a look at the destinations outside 100km away (click on them) and figure out what those trips were for, how you got there etc.

Do the same then for 2017.

You can drill down into each trip or each day if you want by clicking on them, but that is only needed for the long ones, because you can assume your short trips are covered.

DesperateDan

PM me the json file if you want, I'll look into seeing if there's some way I can throw it into excel or something (no guarantees )

Soarer

Or, more simply, if you tell us your average daily mileage, we'll be able to tell you if an ev would suit.

If you want the cheapest way into ownership, you'd want to be doing less than 100kms per day without a charging option.
It'll get progressively more expensive the more mileage, up to and including the Ioniq and new Leaf (next month).

oppenheimer1

Soarer wrote: »

Or, more simply, if you tell us your average daily mileage, we'll be able to tell you if an ev would suit.

If you want the cheapest way into ownership, you'd want to be doing less than 100kms per day without a charging option.
It'll get progressively more expensive the more mileage, up to and including the Ioniq and new Leaf (next month).

Averages tell you nothing useful in this instance. OP needs to know how many journeys were made inside range and outside range. He has all the data to hand so there is no need to guess.

OP you can convert json to csv files with online converters which can be opened in Excel. Search Google json to csv.

Soarer

Fair enough.

Orebro

oppenheimer1 wrote: »

Averages tell you nothing useful in this instance. OP needs to know how many journeys were made inside range and outside range. He has all the data to hand so there is no need to guess.

OP you can convert json to csv files with online converters which can be opened in Excel. Search Google json to csv.

What kind of average distance do you get from your EV?

quietsailor

Thank you all for the replies, sorry I'm so late in replying something is wrong with my home internet as it takes an age to load any individual page. I'm at work now where they have good internet

gally74 wrote: »

Why not start a company. Sounds like a great app opportunity there.

Ha, I can't convert a JSON file to a CSV, can you imagine me trying to set up an IT company

pwurple wrote: »

The easiest eay to do it is to use the graphical interface... read the map.

So, when you go to google timeline, and are logged in with the google account you use on your phone, set the year to 2016 say... leave month and day blank. wait a few seconds, and all your destinations will appear.

You know where you live, so take a look at the destinations outside 100km away (click on them) and figure out what those trips were for, how you got there etc.

Do the same then for 2017.

You can drill down into each trip or each day if you want by clicking on them, but that is only needed for the long ones, because you can assume your short trips are covered.

I know what you mean and I did try it but it's an information overload.

DesperateDan wrote: »

PM me the json file if you want, I'll look into seeing if there's some way I can throw it into excel or something (no guarantees )

Thank you, I'll have to pm you from home tonight as work won't let us access any type of online storage and it considers the timeline data download an online storage

Soarer wrote: »

Or, more simply, if you tell us your average daily mileage, we'll be able to tell you if an ev would suit.

If you want the cheapest way into ownership, you'd want to be doing less than 100kms per day without a charging option.
It'll get progressively more expensive the more mileage, up to and including the Ioniq and new Leaf (next month).

I know what you mean about the average daily mileage - I know my home - work and home - cork commutes are 40KM return for each and that's a lot of my travel, what I need to find out is is it 70/80/90% of my travel

I do think my travel* is well within a BEV range, but I NEED to be able to prove it to my wife.

* = I use the word travel rather than commute as I work shift and would drive a lot on my days off between visiting Limerick, trips in/out of cork to the builders providers and just general other trips.

quietsailor

oppenheimer1 wrote: »

Averages tell you nothing useful in this instance. OP needs to know how many journeys were made inside range and outside range. He has all the data to hand so there is no need to guess.

OP you can convert json to csv files with online converters which can be opened in Excel. Search Google json to csv.

Apologies Oppenheimer1, I did try a few of these already (and should have mentioned that in the OP - I'd like to think that nI'm not that much of a useless lump that I wouldn't try a converter myself first)

quietsailor

This method WON’T work! -- you’ll find that the distance calculated is straight line distance I've put up the info anyway in case it is useful to someone.

In the example below I used three points that are miles apart and the road between is anything but straight, it twists & turns plus it has hills and hollows. If Google Timeline collected data every twenty seconds it wouldn’t matter that much…….But as it seems to collect every 2-3 minutes then this method won’t work for working out the distances travelled in a day. I’m putting it into this thread anyway in case someone in the future reads it, it might save them some time.

Google Maps Image



Corresponding Excel calculations


If anyone is interested for general knowledge here is what I did; a lot of them are interesting for the background but the calculations are far too complex for what I needed – they are people flying long distances who need to calculate the great circle routes.
a. See how to calculate distance in Excel, using Latitude and Longitude, in this tutorial by Excel MVP, Jerry Latham
b. Calculate distances using VBA in excel, also included great circle route calcs

1. This is the actual guide to what I used; Calculate distances using latitude and longitude in excel – easiest to follow and is a straight line calc, no great circle routes

2. Search for "Google takeout FAQ" – explains how to archive your timeline data into a JSON file
3. Convert JSON into excel file – I ran into problems here but another Boards user was kind enough to convert the JSON file for me. On reading more about JSON files I think my file was too big for the free online converters so I should have specified date ranges for the Google takeout and then the free online converts MIGHT have worked – then you’d need to copy+paste all the info into one excel file.
a. Here I’ve just created three random lat/long data points around Cork city – I don’t want to be throwing up my own data as it has my home in it



And here is the actual Google Maps – you can see the same Lat/Long co-ordinates on the map.


4. Now, imagine that there are over 10,000 entries in the excel sheet which there is easily over a few months.
What I will do is find all the distances for one day and add them together.
For me it was easier to split the Time AND Date column into two columns, Time & Date; using the “Split Text into two columns with the Convert text to columns Wizard”, and then you can work on each day on its own. You might not need to do this so ignore step 5

5. Make sure you remember to insert a blank column after column one to let the Time values have somewhere to display – otherwise they overwrite the Latitude data.




7. Once you’ve the Date and Time split into two columns I insert a row after each date – that makes it easier for my eye to find all the data for one day.

quietsailor

7. Next the formulas – there are three here;



a. Calculate the distance between two points on a map – that’s the “Distance Column and you’ll see that the first row is 0 as it is the starting pt = no distance travelled. The formula for the second and subsequent columns is;
=6371*ACOS(COS(RADIANS(90-C4))*COS(RADIANS(90-C5))+SIN(RADIANS(90-C4))*SIN(RADIANS(90-C5))*COS(RADIANS(D4-D5)))/1.609
C4, C5, D4, D5 are the cell numbers
The number it puts out is in MILES between two points on the map
b. Add all the distances travelled in one day together and roundup the answer to two decimal places
=ROUNDUP(SUM(E3:E5),2)
c. Convert the distance per day into Km per day
=ROUNDUP(CONVERT(F3,"mi","km"),2)

Finally, the excel file is attached here as well in case people want to copy & paste into it

cros13

The easiest thing is just to record and reset your trip meter every morning and record a week or two of use.

If you are not exceed 80% of the EPA (or real world) range of the EV you are looking at more than once or twice a week you're suitable.