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

hello friends my name is heman i'm currently a student at nati mobile today i'm going to show you something very interesting if you are a supply chain professional and you are trying to do a route optimization or a vehicle routing or a network analysis logistic cost estimations planning supply chain analysis and anything related to that you will need to deal with multiple locations and when you're dealing with all these kinds of problem what you will need essentially is something called as distance matrix distance matrix is basically the road distance between each and every point how do you get this road distance we all know if we just google search it we will get it obviously but say if you are dealing with 12 different locations the number of google searches you have to do is 66. now 12 locations can be less but say if you have a country level dispatch the locations can go as high as 500 or say you are delivering inside a city the locations can be as high as 1000 in such cases will you be able to do it manually no right so that's where the automation is required that's exactly what we are going to do today on excel on my screen you can see now a distance matrix where locations are listed in rows and columns so in this exercise we should be able to fill this matrix let's get into it let us solve this problem in two phases in the first phase what we'll do is we'll generate only for two locations we'll give a source location we'll give a destination location and we'll try to get the distance after that we'll see how we can fill the matrix so in source location i will give goa destination location i'll give partner just for example now how did i get these coordinates i have a database actually where for each location i have latitude and longitude then i made a concatenated column called coordinates in the format like latitude comma longitude right you will need this so you somehow need to get this so once you have these you need to get the distance to get this distance we somehow need to make excel talk to either google maps or some kind of maps the excel should ask for these two locations i need the distance so what we need is something called an api api is application programming interface which allows two different applications to talk to each other so now we need to make excel talk to either google maps or some other maps now google maps isn't free we need to pay some amount but bing maps is free till some extent about one lakh or more than one lakh responses or requests you can get for free through bing maps so we'll do that we'll somehow try to connect bing maps to excel to enable this api we need something called an api key so the way we do it is let's open the browser now go to bing maps portal you will get to see a window like this now click on sign in and go through your outlook account right you should be able to see this kind of page once you open so now here if you see yes let's create a new account click on that now give your account name contact name company name you can give something i will give my college name now email address you can give an email id phone number you need to give a phone number and check these boxes click on create now you have created an account so what next you need to do is go into this my account click on my keys give your application name i'll give test key application url you can give anything guys that's not an issue you need not give anything even it's not minded so let's not give any uh key type basic application type you can select device or test right then click on create now we have created click on this show key okay this part is your key now copy this key and come back to excel and insert it over here now we got the key all right but how do we make excel talk to bing maps you need to go into developer tab click on visual basic now go to insert module and you need to paste a piece of code which i am going to give it to you this is the piece of code copy paste it here so what is this code basically this code is actually a formula which i created called get distance this guess distance takes input start coordinates destination coordinates and the key in return it gives you the output as distance in kilometers you need to ensure one thing go into tools and click on references and you ensure that microsoft excel 16.0 object library and microsoft office 16.0 object library are enabled these references are needed if you want the excel to talk to bing maps so right after we did this close this window now come here type in the formula get distance that formula should be appearing in your formulas now give the source coordinates comma destination coordinates comma the key which we have generated and now you should be getting the distance in kilometers that's it guys it is as simple as that this formula works whenever you change the location even you just type in either above now the new coordinates have come and new distance has come you can even change over to my source and you can do bangalore okay now we got 143 what a nice number let's verify even whether this is right or no let's go to the browser let's open bring maps now we can see mysore to bangalore is exactly 143 kilometers see guys it works perfectly it even accounts for traffic we have this our step one is done now let's quickly move on to our step two that is generating a distance matrix now how do we fill this matrix it is simple guys when we want to fill this particular cell what we'll do is in source we'll enter goa and in destination also we'll enter obviously we'll get zero when we come here we'll type in source goa and in destination we'll type pune then we'll get what is the distance between goa and pune similarly we'll write a macro code to fill the entire matrix so let's quickly open the visual basic window again and go to insert module type sub distance matrix i'll arrange the windows side by side so that it'll be easier for me so i'll define the matrix range matrix range we'll define it in a for loop for each cell in range range starts from f here you can see it starts from f4 and it goes till q15 so we'll type f4 colon q15 and close the brackets enter just type next cell and we'll insert the code in here this for loop ensures that the code is iterated for each cell over here let me insert a code range p4 dot value equal to cells cell dot row comma column will be five dot value so what this line is doing is it is pasting the location name in v4 which is source cell similarly i will copy paste this line will change this to b5 and over here for destination i will put third because you can see here on the third row we have the destinations and in column will write cell dot column that is when when the cell is over here what it does is it considers third row that is this particular row and our active cells column that is h column then you will get my source so that's the way the logic works so once we paste the source and destination what we have to do is the distance automatically comes over here when cities are pasted over here over b4 and b5 the distance is generated in beat 11 so we'll write cell dot value equal to range b11 dot value that's all this is this is all the code that is required now let's just run this code and see whether it is working or not here you can see on the left the code is getting printed [Music] all right guys you can see now the distance matrix printing is done we got the entire matrix field with our piece of code so to ensure that this process is iterative you need to save this mac this file as a macro enabled workbook otherwise our macro gets deleted so i will just select go to save as and select macro enabled workbook and save it right guys the task is accomplished i hope you guys have really learned something thank you very much

Channel: Hemanth Boddapu

Views: 95,207

Keywords: distance matrix, bing maps, google maps, vehicle routing, supply chain, location planning, network optimization, bing maps api, maps, api, macro, VBA, Excel, nitie, nitie mumbai

Id: 4Trcpq2ldoc

Length: 11min 46sec (706 seconds)

Published: Sun Aug 30 2020