021. Free Workbook to calculate the TIME and DISTANCE between Locations in Excel (Using Bing Map)

Подписаться 7 тыс.

Просмотров 12 тыс.

50% 1

This is the second part of 2 lessons where you will learn the ins and out of the Bing Map API that you can use in Excel. All what you will learn doesn't require any coding! In this lesson, you will learn how to use a workbook (see link) to get the coordinates of a location and to get the distance and time between the 2 locations. Feel free to modify the code to include more parameters in the Bing API call (e.g., Walking instead of driving, start time, traffic, ect...)
*** Link to excel workbook:
mega.nz/file/qaBDVaAC#aObjNLG...
*** Bing API documentation: developers.google.com/maps/do...
*** VLOOKUP: • 036. Everything you ne...
*** Dollar sign: • 062. Basic understandi...
PS: Ensure that you follow the guidelines:
1) Don't use a MacBook
2) Enable all macros (there is a button for it on one of the sheets)
3) Ensure no typos in addresses
4) Use Latitude/Longitude coordinates for matrices
#Excel #MsExcel #BingMap

Опубликовано:

30 мар 2022

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 60

@marufakhotiawan4837 Год назад

Hello, Thank you so much. Your guide is very helpful for me. I have question, Can we calculate Travel Cost using this trick ?

@EXCELWIZARDINMINUTES Год назад

If you assign a cost per km (ie u have an assumption for fuel, depreciation, maintenance, etc...), yes you can. I think this is how applications like uber do it (obviously much more sophisticated)

@marufakhotiawan4837 Год назад

@@EXCELWIZARDINMINUTES thank you, is the travel times depend on the current situation of the traffic condition ?

@EXCELWIZARDINMINUTES Год назад

@@marufakhotiawan4837 using the api, u can check the parameters and i think u can speficy this

@DavidCleal-jz4ok 3 месяца назад

Thanks for creating this video - it's very helpful. I've downloaded it and added some local Australian addresses but Bing is replying with US lat and long giving low results. Can you tell me how I can debug this issue?

@EXCELWIZARDINMINUTES 3 месяца назад

U might have to be more specific with addresses (it s probably not catching the names), or u get ur lat long on google maps and use them in the distance and time matrix. Try one address on google maps (not excel) and see what it gives for lat long. That might tell you why it s not catching it properly and you can correct accordingly coz the api works like google maps.

@ihsanridho8721 3 месяца назад

Hello, thank you for making this video. I have been trying to use the excel that you have created, but the distance produced is an integer. For example, the real distance is 5.3 km but what appears in excel is 5 km. Or 4.4 km becomes 4 km. Could you help me with this?

@EXCELWIZARDINMINUTES 3 месяца назад

You will have to go to the code. There is a formula that returns the distance with round. There u might have to do modifications. (Search for round in the the vba code)

@VulcanTrekkie45 6 месяцев назад

So I'm trying to use your worksheet to generate a distance matrix for some locations in Japan, but all I'm getting out of it are cells that just read 0. Is there something going on here that I'm missing? How can I fix this? I know for a fact none of these values should be 0. I changed the coordinates to locations here in the US and it came back with the correct travel distance, so I'm not sure what's going on here.

@EXCELWIZARDINMINUTES 6 месяцев назад

Most probably the api is not recognising yout japanese locations. What you could do is find the lat and long on google maps and input them

@VulcanTrekkie45 6 месяцев назад

@@EXCELWIZARDINMINUTES I was using raw geographic coordinates though. Maybe Japan isn't supported or something?

@EXCELWIZARDINMINUTES 6 месяцев назад

@@VulcanTrekkie45 it should work where google maps work. here are some ideas: 1) Try another city like paris to see what happens (Put the coordinates in case it doesnt recognise) 2) try some landmarks in Japan to see what happens 3) Read the API documentation to check if there are restrictions for different countries 4) Ensure you have an up to date key. Maybe you are using mine and it s already expired

@verrelnathaniel2741 3 месяца назад

Hi, Thank you for the really helpful turtorial but i am really wondering if i need the data to be in meter and not rounded will it be possible? Thank you so much!

@EXCELWIZARDINMINUTES 3 месяца назад

You can convert between unit of measures easily. For the rest you might need to check the API documentation and potentially make modifications to the code

@EXCELWIZARDINMINUTES 3 месяца назад

i just remembered, there is a line of code in VBA: GetDistance = Round(Round(WorksheetFunction.FilterXML(objHTTP.responseText, "//TravelDistance"), 3) * 1.609, 0) This is where you can play with round and the meters (1.609).

@verrelnathaniel2741 3 месяца назад

@@EXCELWIZARDINMINUTES Thank You, what about mode selection is it possible to change it to cycling method? Thank you so much for your feedback btw!

@EXCELWIZARDINMINUTES 3 месяца назад

@@verrelnathaniel2741 Somebody asked me about this long time back in the comments. honestly, i ve never done it. here is what you can do. in the VBA code, there is a http request (ie some line with http) that calls the API. Have a look at the API documentation, I am sure they will tell you where you have to change this to get cycling, walking, etc...

@RickTownson 4 месяца назад

Hi, First of all thank you very much for this. However, I am experiencing the same problem as a couple of others in that I am getting the #VALUE error. The weird things is it was working perfectly and then just stopped. I thought oh well I will go back to older versions of the sheet I was working on but they too gave the same error. I then went all the way back to basic and re downloaded your original file. It too suffers from the same problem. I thought it must be the Bing Maps Key. Nope, I have created 2 more and tried them both and neither work :-( 😢 PLEASE HELP ;-)

@EXCELWIZARDINMINUTES 4 месяца назад

it is very difficult to know what u might be doing wrong but plz read the previous comments where i suggested some reasons (some have wrong coordinates, non working keys) and what to do. worst case scenario, u can check the documentation of the API. So far all the ppl had problems related to their sheet and not the API so there is hope :)))

@elvisancans3308 Год назад

Thanks! This is very useful! Would be very thankful if you could help me with one thing - I tried to add start time in the macros, however, it doesn’t work for me (shows the same time), I was wondering, maybe my time format doesn’t work? Maybe you can tell me more how this should be addressed?

@EXCELWIZARDINMINUTES Год назад

As you get the number if min between places in excel, you could simply add the min to an excel formula and get the answer. I think that is easier than trying to change the code. What do u think?

@elvisancans3308 Год назад

@@EXCELWIZARDINMINUTES huh, sorry, a bit hard to understand for me - do you mean that I should, for example, multiply my current result by some kind of coefficient to get a time result for start time when it is a busier traffic?

@EXCELWIZARDINMINUTES Год назад

@Elvis Ancāns no. I mean in the sheet distancetimematrix, you have a formula that gives the time between 2 locations. (GETtraveltime) This is the num of min between 2 places. U can write an excel formula start date and time + the result of the formula/24/60

@elvisancans3308 Год назад

@@EXCELWIZARDINMINUTES Okay, I see. Basically I meant something a bit different - I need to get a travel time, however, I need to get it for, for example, 6pm, when the traffic gets busier, thus, i need to introduce startTime variable in the macros. Because, currently, travel time that I get is for a traffic when it is not busy. (For example, if I compare travel time with my excel result and the one on bing browser at 6pm, then of course it will be higher on Bing Browser version at 6pm)

@EXCELWIZARDINMINUTES Год назад

@Elvis Ancāns in this case you jave to check the documentation of the api. I will give you a link but this is an example of a call: dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins={lat0,long0;lat1,lon1;latM,lonM}&destinations={lat0,lon0;lat1,lon1;latN,longN}&travelMode={travelMode}&startTime={startTime}&timeUnit={timeUnit}&key={BingMapsKey} You can see the start time in it. learn.microsoft.com/en-us/bingmaps/rest-services/routes/calculate-a-distance-matrix

@BlackbuttPartners Год назад

Hi. I downloaded "BingMaps - v1.xlsm" and have entered data into the "Geocode" sheet and updated the formulae in the "DistanceTimeMatrix" sheet. Unfortunately, when the functions (GetDistance and GetTime) run they produce some very odd results e.g. London to Edinburgh 0 km and 0 minutes. Can you advise what might be going wrong?

@EXCELWIZARDINMINUTES Год назад

Hello Markus, I have tried it and it works. Let me give you some tips 1) Get a Bing Key (that could lead to the code not working) 2) Enable macros in your excel (if you don't the code doesn't run and you get nothing). 3) Get the right lat and long (mega.nz/file/yDJ1FLzA#oe93nKYEkCeTPIU_ZE1AxSqpx2MAkj9UhE57c1LGXg0) 4) Put the lat and long in the last sheet and update the table headers (mega.nz/file/yDJ1FLzA#oe93nKYEkCeTPIU_ZE1AxSqpx2MAkj9UhE57c1LGXg0) and (mega.nz/file/uKIzETLa#JdLq4X842KANw0Gb7D2pOEO3hYc9s3UYez7P7hpwnwY)

@MarkSmith-bj2gf 6 месяцев назад

Hi, can I get the link to Part 1 please

@EXCELWIZARDINMINUTES 6 месяцев назад

I think you are looking for this: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-UyW5I-F5KG4.html I will also respond to your other comment. There is a way with VBA

@StiC890 5 месяцев назад

Hi, I tried your file, it can find my lat and long, but when I enter those in the table in the matrix, add the formula I get #VALUE errors. Without modification to your initial latitudes and longitudes it does calculate your distances, but not when I copy from the generated list to that matrix. So for some reason it cannot read the lat and long correctly I guess? Could this be the language setting in Excel? I'm using the Dutch variant, so my functions are Dutch, it needs semicolon instead of colon in these formulas to add new parameters... How could I change that if that might be the problem?

@StiC890 5 месяцев назад

Found it: due to the settings being semicolon instead of colon, the results could not get matched. I changed my regional settings and it works. Thanks!

@EXCELWIZARDINMINUTES 5 месяцев назад

It could be that you need to put a valid key or u have to use the formulas in english. In other languages, u might try the appropriate syntax. Also make sure the lat long are in the right order. Fibally, you could put a break point in the vba code and once u enter the formula, u can use F8 to follow the code and see what u are doing wrong (you can see if 1 of the inputs is wrong)

@EXCELWIZARDINMINUTES 5 месяцев назад

Ahh ok. Just saw ur other comment. Yeah, it was one of the things i mentioned to try. Glad it works now! :)

@meryemwardani6769 2 месяца назад

Hello, thank you for this helpful video , is it possible to change the adress that is listed in the Excel that you provide ? because i have tried to change it but the formula only works on the adress you gave and not mine and if it's possible can you please tell me how i am trying to modify it so it can give me the distance between adress ( street postal code city)

@EXCELWIZARDINMINUTES 2 месяца назад

Hello, yes of course. You have to look at the comments before yours. Some ppl had issues and i listed all the possible options (such as not having a working key) i know about.

@meryemwardani6769 2 месяца назад

@@EXCELWIZARDINMINUTES Thank you for your time , the Problem i am facing is not listed in the commets before because i do have a working API key but when i use the formula to calculate the distance with the adress that i enter (form : street postal code city) it's always the #VALUE error

@EXCELWIZARDINMINUTES 2 месяца назад

@@meryemwardani6769 try to use a normal address to get the latitude and longitude. for example Paris. Check if it works for you.

@EXCELWIZARDINMINUTES 2 месяца назад

Sometimes, if the address is vague, it doesnt give you the lat and long

@meryemwardani6769 2 месяца назад

@@EXCELWIZARDINMINUTESit gave me the latitude and longitude with no Problem but it does not give me the distance between them.

@galaxysounds9334 8 месяцев назад

Is it possible to look at the distance and time between two columns on this spreadsheet, every time I try keep getting VALUE! error

@EXCELWIZARDINMINUTES 8 месяцев назад

I am not sure I understood what u need. Have u got a new key for the API?

@galaxysounds9334 8 месяцев назад

@@EXCELWIZARDINMINUTES Yeah I have a new key, however I am looking to build it in to column where you've done it in a table if that makes sense, also as a side note I'm using Excel for Mac if that makes a difference.

@EXCELWIZARDINMINUTES 8 месяцев назад

@@galaxysounds9334 Ok, I understood now. I can't tell you for Mac since I don't use Excel on Mac (It has limitations), however, what you are trying to do should be possible. All what you have to do is use the formulas that are in the video (GetDistance and GetTraveltime). For example, for getdistance, you have to feed is the latitude and the longitude for each of start and destination and you have to use the bing key. Here is what I suggest you do to find the problem: 1) Use my workbook with my data and see whether you get the right info using YOUR key - If you do, then the key is ok 2) Use my workbook and write 1 formula in any cell using my data (my lat and long) - if it s ok, then you are fine 3) Write 1 formula with all your parameters and check - if it doesn't work, then you have a problem with lat and long 4) Try all this on a Mac to see if Mac is an issue

@bsabrunosouza Год назад

I am in South America and here we use the meter (m) unit. How can I modify the code for such a unit?

@EXCELWIZARDINMINUTES Год назад

Hello Bruno! Basically , you don't need to modify the code. The excel formula returns the distance in km in the formula. You just need to multiply the formula by a factor to convert it. From km to m, you multiply by 1000 for example

@bsabrunosouza Год назад

@@EXCELWIZARDINMINUTES Thank you very much! If I want to add two decimal places, how can I do it?

@EXCELWIZARDINMINUTES Год назад

@@bsabrunosouza you can change the format of the cell but am not sure the code will return to this level of precision

@EXCELWIZARDINMINUTES Год назад

Actually if u go to the module distancetime, u have a line in getdistsnce that rounds the number. There u can play with it to get what u need (getdistance = round(....))

@SandraReid-tl3iq 4 месяца назад

Such a shame I can't get this spreadsheet to work on my Mac? Why is that? I keep getting VALUE! coming up? My API key is working as I get the correct Lat & Long on the Geocode tab. Is this just a Mac thing? I know the formula is spot on. Weird?

@EXCELWIZARDINMINUTES 4 месяца назад

Am not an expert on mac but i can tell u that vba doesnt work exactly the same on a mac (for example i know that some form controls are not there). Have a look at the previous comments and see the common issues that ppl face. Maybe that could help. Last resort would be to test it on non mac and see if it s a pc issue

@SandraReid-tl3iq 4 месяца назад

@@EXCELWIZARDINMINUTES thank you for your reply. Sadly I think it must be a compatibility issue with the Mac. Hopefully someone else may be able to help me out if they are also having issues with Apple products!