How to calculate Driving Distance Matrix on Excel using Bing Maps API

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

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

50% 1

This is a small tutorial video on how to generate a distance matrix on excel using Bing Maps API. Everything is explained in detail. This video is extremely useful for a Supply chain professional who is trying to perform Vehicle routing / Network analysis / Logistic cost analysis / Location Planning / Logistics Planning. It provides the road distance along the best routes considering traffic.
Link for GetDistance formula: drive.google.com/file/d/1zfrQ... (for windows version)
This may not work in Mac OS, due to limitations of Apple Mac.
If you need Travel time aswell, post this below code in your vba window and use GetTravelTime formula in the same format as GetDistance as explained in the video.
drive.google.com/file/d/1Dyao...
output will be travel time in minutes
If you are getting an error, it means the formula is not giving you any relevant information.
Here are the general reasons which I know result in such errors. (in the order of the likelihood)
1. If you are using a macbook. The Excel in macbook does not have a web service function, which is essential for Excel to talk to the internet.
2. If you are using Windows, but on a work laptop where Excel application may not have access to the Internet service. To validate if this is the case, I suggest trying the Webservice function to see if you are getting a relevant output. If you are getting an output, this may not be the reason. And if you are not getting the output, this is the issue and you need to talk to your IT admin or do this on your personal laptop. Reference: exceljet.net/excel-functions/...
3. If you are sure 1&2 are not the reasons, There could be an error in the source coordinates and destination coordinates or the Bing key that is inserted into the GetDistance formula. Request you to kindly revisit the video and verify.
4. You need to save the file in .xlsm format.
About me:
I am a student at NITIE Mumbai, batch of 2019-2021. NITIE (National Institute of Industrial Engineering) is a leading academic institution in generating world class supply chain and operations professionals. It is also popularly known as "The Mecca of Supply chain".
Link: www.nitie.ac.in
#bingmaps #distancematrix #excel
To Connect with me:
Email: hemu161095@gmail.com

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

4 авг 2024

Ссылка:

Скачать:

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

Добавить в:

Мой плейлист

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

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

@YogeshSureka 3 года назад

Very structured & elaborate explanation. Must watch for all aspiring learners.

@lucastavares3470 3 года назад

You have help me a lot ! I’m making a cost sheet , using Kilometers from every city from state of São Paulo

@fulyaulutas4599 Год назад

This is exactly what i needed. Thank you for explaining very clearly

@spvgggwdeg Год назад

This is amazing and very, very helpful for me. Thanks a lot for the well elaborated explanation.

@himanshushukla3454 3 года назад

Thank you Hemanth for such insightful videos

@srinivasannarayanaswami5378 3 года назад

Thank you Hemanth. Lucid explanation and a very useful tool. Much appreciated

@HEMANTHHEMU86 3 года назад

Thankyou sir

@vidaan95 Год назад

Hi Hemanth, this is a wonderful video. It really saved a huge amount of time for me.

@philcoul6905 3 года назад

Good work. This exactly what was looking for. Keep up.

@rajaaditya0021 2 года назад

Awesome sir! Helped a lot during SIP... Thank you so much!

@sudheerkumar6427 3 года назад

Good work Hemanth Boddapu... expecting more videos from you..

@widhyawanjuliaris3939 Месяц назад

thank you very much for the knowledge. you've helped me in many ways.

@sohamdsouza 3 года назад

Great work Hemanth !

@Bhoomisolanki0210 Год назад

Very Helpful video, Thank you !

@catquynhnguyenle3604 3 года назад

Thank you so much for providing this video

@TheArtlessArtist 3 года назад

Lovely work Hemanth Keep up the good work ❣️

@leroyjenkins5679 2 года назад

Excellent hemant, just one observation though - Since api keys have limits, every call is precious. Hence filling only the upper half of the diagonal would have been wise. Also from efficient programming perspective it is important

@blue0152 2 года назад

THANK YOU! You did miss that input for Source and Destination coordinates. Also you could have clarified the best way to get the coordinates in the database tab. OTHERWISE, this is an excellent video and I really appreciate it!

@jamesa7576 3 года назад

This is awesome! Thank you

@siddharthamodukuri7112 Год назад

Thanks Hemanth, Very helpful

@RodrigoSebastianBuchiniz 2 года назад

Genial! Gracias muy útil y muy bien explicado!

@gouthamsolleti4316 3 года назад

Good work bro....working perfectly....

@ssaravananp1 3 года назад

Dear Mr.Hemanth, It was a useful App. We have around 25 rows and columns in our Application. The macro works correctly upto 12 rows and columns. From 13th row and column, there is a difference of at the least 2 Kms. Please guide us. Regards, S.Saravanan

@grizzoff 3 года назад

Amazing! Thank you!!

@faisalalkaabneh4020 3 года назад

this is amazing, thanks a lot :)

@TrudiDay Год назад

Awesome - thank you!!

@lucastavares3470 3 года назад

Omg man , awesome work

@alexanderbarlse2875 2 года назад

Hi there! Thank you for this great piece of code, it helped me a lot. However, i'm having a small issue. The code was running perfectly well yesterday. However, men reopening the xlsm file today, and trying to use the getdistance function, I get the error: #NAME?. Do you have any suggestions? Thank you.

@Suman-vv5gv 11 месяцев назад

Thanks for this Video ...

@saikiran-zn4cq 3 года назад

Thanks dude.

@prafullgoyal24 3 года назад

Awesome...I need to hit like button for tonnes but have to satisfy with 1 only..✌

@HEMANTHHEMU86 3 года назад

Thankyou buddy 🤟🏻

@jacky_23 3 года назад

Good video !!

@guonorman51 Год назад

Thanks for your video clip

@lucastavares3470 3 года назад

Greetings from Brazil 🇧🇷

@bubblesarita1428 Год назад

Excellent👍

@ambarukanasubrahmanyam51 Год назад

excellent video

@eetigupta7840 2 года назад

Hi, your video helped me a lot in my client deliverable. Thanks a ton. Is there a possibility to get the travel time for a particular date and time? I am looking to calculate travel time for historical date and time

@meetyadav416 Год назад

Awsome dude 😮

@hakanyldz9977 3 года назад

Can we draw route line inside the excel bing map for to make travelling salesman problem?

@nicolesamson380 2 года назад

This works perfectly, Thank you so much Hemanth. When im working on a larger data set it takes quite a lot of time for it to fetch the distance, is there any work around for that? TIA.

@TheNitoperez Год назад

Desde Mexico.... funciona al 100%

@Antizokker Год назад

Thanks for the Video! But how do i get the field like B11 and which are necessary for the automasation to fill out the Matrix??

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

Thank You

@karstenhaberkorn6182 3 года назад

Hello Hemanth, this is really very helpful and a great tool - thank you for that! Can you also advise, on how to get coordinates (latitude and longitude) back from bing maps for given locations? This would be very helpful, in case you need coordinates for many addresses (comparable to the list of locations you put into the "database sheet"). Many thanks for any advice! Karsten

@jacky_23 3 года назад

I had this problem and used power query web connector

@sherifhassan1556 3 года назад

Hemanth I hope you're well. This is an excellent tutorial, thank you. Unfortunately, I keep getting #NAME? error when running the GetDistance macro. Any suggestions? Thanks again. Syh

@sergio_molinari 2 года назад

Hello great tutorial!! I noticed that it considers the Highways, how can I not consider them in the search? Thank you

@josegutierrez4969 2 года назад

I LOVE YOU

@anitasunildesai 2 года назад

hello Hemanth, can we do the same giving lat and long instead of name of cities? how?

@2909remco 3 года назад

Thank you for this great video. Would it be possible to also provide the associated GetTravelTime code? Much appreciated

@aphastus 2 года назад

Wow I tried not knowing if it’d work because I’ve never coded in VBA and I did a couple mistakes that I was able to detect and understand watching my code and yours (mostly cells placements). I used the time instead of the distance. It is incredibly satisfying watching the code fill all the cells in the matrix. I started in Mac but the getdistance formula didn’t work. I’ll try it later to see if something wasn’t correct. What bugged me is that every version of office uses different separators, you used a , and I had to change it to a ; so probably it’s the same problem with Mac. Anyway, thanks so much for the video, it was so nice!

@HEMANTHHEMU86 2 года назад

Hey.. the issue with mac is that excel in mac cannot talk to internet. Pls try with windows. I've mentioned the same in the description box

@WorkEasyGo Год назад

Hi How did you connect database to sheet one. Can someone please help me out with the formula.

@sidneyro5554 Год назад

Hi, excellent tutorial. Would you help me .the negative coordinates of the error how do I?? positive coordinates works. thanks🙂

@MrJaawt Год назад

right now the calculation automatically grabs the shortest distance and not the interstate distance. how could I change the VBS Module to show the shortest interstate distance? But thank you very much for this so far

@rasraster Год назад

Very nice, thank you. Can this be done with HTTPS instead of HTTP?

@tommyexceltricks7665 2 года назад

Hello , Thanks for this tutorial can i ask about something Q1:why ther's diffrence between bing maps and Google maps Ex: two cordination ( lat,long) in bing maps : 16 Km -- Google maps : 13 Q2:some times result returned from bing maps doesn't exact the result already in bing maps if you switched to bing maps and tried to get direction to comapre result in excel file with data in bing maps thanks in advance

@irenesolmone7667 Год назад

Hi! I used the formula and everything worked perfectly well for a while. Now, excel isn't recognizing the formula anymore. Do you have any advice on how to solve this?

@alejandrapinoncely7040 2 года назад

Hi! great job! The code is working but not for every city. I am having trouble with cities in Asia, more specifically within China, Japan and Korea Could you help me with that?? Regards, Alejandra

@katieallinson5781 3 года назад

Hemanth - please please can you tell me how to make bing maps return the longest distance between two points.

@dindazalfa 2 года назад

Hello Hemanth, can you help me to make vba code for calculate distance and travel time without toll usage in BingMaps?

@narejo223 2 года назад

lastVal = "&travelMode=driving&o=xml&key=" & key & "&distanceUnit=mi" if for meters than what will be correct code instead of miles

@ranjitsakhare Год назад

Hemant thanks a lot. Its helping a lot. Like function GetDistance can you help with GetTime as well?

@hasinrizwan5724 2 года назад

Great video brother, Very helpful for me... Can you tell how to convert this Excel data into numpy array?

@TomCat-hp6mi Год назад

I want to do something similar. I want to know the distance travelled by the train from station A to station B. Please tell me how to do it.

@RafiqueNoorie 3 года назад

It is not mapping the distance which you was taken an another excel sheet2. How we can merge into excel sheet 1?

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

Hi.. Can we get the distances by pincode to pincode

@matinamoozadeh235 3 года назад

my excel shows the error Sub distMatrix does not define, what is the problem?

@superintendentofpolicememb3560 3 года назад

Thanks but I follow your instruction but GetDistance formula not shown in Excel-2019

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

how to do this between pincodes within the city?

@subhendupradhan6475 3 года назад

it is throwing an error "invalid outside procedure". Please Help

@gokaykkaya 7 месяцев назад

can this method be used for 80 locations?

@DAANoLOOF 2 года назад

Hi Hemmath, is the distance calculated over the road or in a straight line? Either way, thank you for the tutorial!

@NathanaelHarwood Год назад

Just for others' sake - the description states 'It provides the road distance along the best routes considering traffic'

@arvindshaker1984 Год назад

hi it was used full to me but it is not working on my excell pls give excel which is red to used

@adrianesc7706 Год назад

What if i don't have the coordinates?

@osamarizwan1635 2 года назад

how can we get the distance in meters?

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

This code is working for India's coordinates. But it is not working for USA coordinates. Please help.

@Michael-zg5cx 20 дней назад

Hey Great work! I used your code for two years now. But with the change to Azure Maps I don't get this think working anymore... can you make a new video where you explain how to use Azure Maps API instead of Bing Maps?

@Alphabah95 2 года назад

Hello, I found your video very helpful for my project, I am a master's degree student. I had some issues linking the geographical coordinates to the distance Matrix like you did, can you explain to me or send me the excel file you did the demonstration on RU-vid? so I can refer to that to build my own matrix? Thank you your help will be really appreciated.

@michalzila7084 Год назад

I tried it and i doesn't work. Variable not defined.

@unnikrishnansanthosh 2 года назад

hi Hemanth, thank you very much for sharing such an useful tutorial, great work. I would like to know whether you have a tutorial for calculating air distance

@unnikrishnansanthosh 2 года назад

Also, one more query, what is the base for calculating travel time - like is it based on speed limits of the roads or peak time / off peak time traffic on the roads

@HEMANTHHEMU86 2 года назад

time is given by Bing, based on the traffic data. just like Google provides on estimated time.

@SiBull Год назад

How to get coordinate automatically trough excel?

@TAMALCHOUDHURYROY 3 года назад

Hello Hemanth, In my pc excel 2013 / office 365, formula "=getdistance" is not showing Can you please help me how can I resolve this issue?

@HEMANTHHEMU86 3 года назад

I have provided a peice of VBA code in the description. Please do paste it in your vba window.

@Gabriel-Oliveira 3 года назад

Hi Mr. Boddapu. Firstly, I really appreciate the time you spent recording this video. It was really important for my understanding when it comes to create a distance matrix. I have a question, if I have 12 locations, I think the total number os distances would be 144 (12*12) instead of 66, am I right? Cheers from Brazil!

@HEMANTHHEMU86 3 года назад

yes 144, that's correct

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

Hey, why the marix is not symmetric ?

@almemo9 2 года назад

Juest amazing, you save me two days of work, just an small question, for big volume of data, the formula gives me #value message, any ideas? thanks in advance

@ankurdikshit4669 3 года назад

followed all the steps, still after using formula excel is returning #value! error, may i know how can it be resolved

@puzzleswithnik8348 3 года назад

same

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

Can you share the link....of the code pls......hyperlink is missing

@ghanshyamsinhdvadher3119 2 года назад

Thanks great help, can u check the same can be converted into AddIn, i was unable to do it

@ghanshyamsinhdvadher3119 2 года назад

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-XnZ1Vz4GvqY.html, this would be helpful

@rohitmishra4253 Год назад

where will i get all coordinates ??

@giannacardenas6189 3 года назад

Hi! This API has a limit of requests? I want to know if I can calculate the distance in a matrix 480x480 long 😬😬

@HEMANTHHEMU86 3 года назад

It has a limit of 1,25,000 requests. If you want to get 480*480, its better if you can write code for either upper half of matrix or the lower half only

@user-ns4tw5lw1r 25 дней назад

Why we used this all it's all available in map when we enter two cities distance in map it's showing

@swaaditya 3 года назад

The tool is great however, can we get the longitude and latitude automatically from bing maps or google maps? As, for all 1000 plus service locations we cannot manually input them right?

@HEMANTHHEMU86 3 года назад

If you mean getting coordinates from addresses, there are similar ways to do it. But it's accuracy is low.. i have observed it gets offset by 10-12 kms sometimes

@swaaditya 3 года назад

@@HEMANTHHEMU86 If thats the case then for each and every address there needs to be an input provided manually. Please share further insight by mailing me on swaaditya@gmail.com. I would be of great help as am trying to work on route optimization in my project as well

@SaurabhPratapiitkgp 3 года назад

How you are getting the source and destination coordinates

@HEMANTHHEMU86 3 года назад

Like i said in the video... I already have them. You can get the coordinates through the Bing maps API aswell.. but it isn't that effective. There are many open source websites with coordinates for each pin code. You can try that.

@gowthamseshadri5118 2 года назад

How to get the coordinates for any locations using API. It is really difficult to get lat & long for every locations manually. Please guide me.

@HEMANTHHEMU86 2 года назад

It's not difficult. There are Google and Bing APIs for that

@renatobeltrame5403 2 года назад

Hi Hemanth! Thank you for sharing!! I have a few questions I think you could help. For the application in excel that I'm developping I need the distances to be showed in meters and with the most accuracy possible. Reading your code I understod that the distances are being calculated in miles and rounded to no decimals. Am I right? If your answer is yes, could you help me to do these adjustments? Thank you very much!

@renatobeltrame5403 2 года назад

Hi, it's me again! Reading the code with more attention I realized that you already converted the dimensions to meters and I changed de round rule. Thanks

@ronnieencarnacion5729 3 года назад

Excel returned #value! when computing for the Distance. Followed the procedure. Concactenate and keys are correct. May i know if this is common issue and if it can be resolved. Thanks.

@HEMANTHHEMU86 3 года назад

If you are using excel on windows, this should not occur. once revisit the steps i have mentioned and make sure internet is active. Let me know

@reaper_gaming_live 3 года назад

Same error with me how can we know if this not getting blocked due to some firewall issue etc

@HEMANTHHEMU86 3 года назад

@@reaper_gaming_live pls follow the steps. It works

@farooqa114 3 года назад

I think error is due to the concatenate function. I used the cancat function but Hemanth used the & sign while getting the X Y coordinates....Kindly explain the destination and source coordinates please.

@jairopontes81 3 года назад

I was having the same issue. It happens that I use metric system in my pc, in which the decimals are separeted with a coma, not a point. I changed these parameters in the options tab and it worked!

@hakanyldz9977 3 года назад

After you copy and paste key password on the cell B9 then automatically appeared word "KEY" on cell A9. How do it?

@HEMANTHHEMU86 3 года назад

It was not automatic.. i have written it in that cell.. i have fast forwarded the video at bit over thete

@uyenphamthithuuyen1903 2 года назад

Hi, your video is really helpful. However, I don't know how to obtain the longitude and latitude from Bing Maps. Can you create a video how to do that, please?

@filippomauro4846 2 года назад

in excel you can write the location in a cell (es. Rome), than select the cell that contain the location, go to data-->data types-->geography and extrapolate latitude and longitude clicking on small icon that appears next the location cell. In a third column you can use the function =concat(cell latitude, "," ,cell longitude) and you have the coordinate of location

@younessfrichi2156 3 года назад

Thank you for the video. Please how can we get the travel time instead of the distance?

@HEMANTHHEMU86 3 года назад

Very simple, in the code i have attached, towards the end of the code, there is a phrase "//TravelDistance". You need to replace it with "//TravelDuration" and remove 1.609 multiplier

@younessfrichi2156 3 года назад

@@HEMANTHHEMU86 Thank you. It works :)

@nanakipshidze3898 3 года назад

@@HEMANTHHEMU86 thank you for great video. is it possible to calculate walking distance instead of driving distance?

@khinthiri1061 2 года назад

@@younessfrichi2156 Hello!! I also want to calculate the travel time. Could you please give me some reference coding for calculating the travel time instead of travel distance?

@detisaskia9918 3 года назад

Sir :( my distance wont show up Do i need to convert my lat,long first ?

@HEMANTHHEMU86 3 года назад

I am unable to figure out your exact problem. Could you mail your file along with your exact issue to me at hemu161095@gmail.com

@detisaskia9918 3 года назад

@@HEMANTHHEMU86 i send the email sir. Kindly check ur email :). Thanks a lot for ur help sir

@infinitedata7088 3 года назад

It is very useful, but Im facing a error actually my all study locations are within of 4 km range. I need accurate ans in upto 2 decimal.

@HEMANTHHEMU86 3 года назад

In the code i have given, there is an equation similar to the one i have pasted below. Just replace this line with the existing line to get a more accurate reading. GetDistance = Round(Round(WorksheetFunction.FilterXML(objHTTP.ResponseText, "//TravelDistance"), 3) * 1.609, 2)

@infinitedata7088 3 года назад

@@HEMANTHHEMU86 Thank you very