 Hello everyone and welcome back to another tutorial. If you're new to the channel then hit that subscribe button So you receive all the future updates of our new videos and also hit that bell notification button So you're notified as soon as those new videos come out and lastly as always if you do enjoy this video It would be much appreciated if you give it a like Not only shows me what you want to see more of and what content you like But obviously it does help out with that YouTube algorithm. So in today's video, we're going to be looking at Sourcing data from the internet So in the example we're going to use today, we're going to do a currency conversion with data from the web So let's step steps through what I'm trying to do and then the reason why it is beneficial to get it from the web So I'm looking to do a currency conversion. So let's say I've got a hundred Pounds and we'll call that we'll put this put a little symbol in here So we can see what I actually don't even need to do that So that's the amount is a hundred and I'm just going to send to all these as well just to get the formatting right and this local currency is Pounds so GBP and I want to convert that to a US dollar amount. So I know off the top of my head It's around about 1.29 dollars to the pound. So all I need to do here is I go 100 times 1.29 Gives me what the dollar equivalent is of my 100 pounds So that's great Obviously if you just do one simple conversion, it's very simple to do as a one-off But if you're going to have if you're looking at multiple currencies And this is maybe something that you want to update daily This is where it could become very beneficial to have a source for your currency conversions And obviously something was going to automatically update rather than you having to go through an update each day The new exchange rates for every single one of those currencies So this is really simple feature built into Excel All we need to do is we go into our page where we want to actually store the data and all we need to do is Well, actually before we do that before we put it in there first I was going to draw attention to this website. So the website is xe.com and they have the section for currency tables So obviously you can do the different select drop-downs depending on what currency you want to exchange to And also what day you want to do so the real benefit of this as well If you want to go back in time and look at historical amount you can use this else you can just get it Obviously for the current day in question So all we need to do want some on this page and you can see the URL up here if you want to copy that out It's just copy this address and Then we can step back into Excel. I Want my data to be in this data tab or the sheet already renamed as data So I was going to go into cell a1 and I will go to my data tab and then from here I'm going to go to get and transform data, but select the option here from web That will then bring up this pop-up and we don't need to worry about advance We're going to stick with basic here. So in here. I was going to enter the URL, but I'll just copied and Select okay Excel is now going to be looking at that website and obviously connecting with it to look at what information is stored on that page We've got two options here We can either see a table view so just tell us the information or we can actually see a web view So it might be a bit easier if you're trying to navigate the website to find what you're looking for But all you need to really do is go to table view and you can step through each of the ones or each of these display options It's found and you can see there's some information here and the document We found live currency rates So that's obviously a table to show in currency conversions for those few currencies But what I'm really after is this table zero here and You can see that by selecting that it's actually picked out the whole table of all the exchange rates that we're interested in Then we'll just go back to that page. You can see it's this table that we've got listed here So it's literally just sort pick selected out the table and its contents So I'm happy that that's what I want to go with all I need to then do is click the load button And Excel now obviously start sourcing that information and it will pull it into Into the sheet for us and what I've done there is I've made a mistake It's obviously put it into a new sheet so I can get rid of my data one because it's not where it is I intended but then if I go into sheet for and I could just now recall Remain this data as I intended all I now need to do is when I go into my currency convert Well, let's call this one conversion Is rather than having to do this manually all I now need to do is go a little V lookups So let's do V lookup. I want to look for that currency and I want to look in my data table and This you can see GBP is in there in row four and because I'm converting two dollars I just want to get this last one here is a column D. So I need to get the value from the fourth column and Then you can see I've got my exchange rate there and So pull the exchange rate. So all I now need to do is go equals this amount Times the result of that V lookup and there is my US dollar amount And then what I can do is obviously just tie this up with a bit of formatting And it's as simple as that and then again the benefit with this is I can now go and select any other Currency of interest. So let's go for Mexican payee. So that's MXN And the currency conversion is automatically going to update for us and ultimately if we attempt to have other values down here as well Let's pick some random numbers Let's just pick the copy the formatting down again because that if not, it looks really horrible on the eye and let's say euro Can't think of any occurrences now stop my head Let's go IDR Indian rupee and in one last one just went over example. Let's go somewhere else down here Let's go for That's so many choose from isn't it Trinidad Trinidadian dollar just for lack of wasting any more time doing this So they're TDD and also when we copy this formula down is then going to update for us So a really simple way of doing that and we can see that we've also got a very small amount of money here So there you go We can see the pence value for that trend that conversion in row 8 Again the benefit of this obviously if I refresh this data nothing's going to happen because it still has today's rates But if we were to put in some incorrect information, so let's say for euro Let's say actually I put in there. I know it's 50. So for each pound There's 50 euros will be a nice conversion there. You can see at the moment That's completely blown out of proportion our calculation here. So it's saying that 120 pound is the same as 6000 euros Obviously it's messing our data here. So all I need to do here What's it what works both one this demonstration really shows that obviously how it refreshes the data and obviously how it's going to change But ultimately if you then make mistakes or anything is overwritten in your table All you need to do is refresh it to correct it. So you see what the 50 at the moment most simply refresh this You can see that Excel will go off now contact the Xe.com website and pull through those new values And there we have it and this is not limited to just Currency tables in this example, you can do this with any And any other data source that you acquire upon obviously dependent on the website there might be some formatting issues as a potential So it's by no means a failsafe. It's going to work for every single thing But it should work for 99% of the information you need So if you're trying something and you find a new data source and you have a great result with it Please leave us a comment underneath this video because I'd love to hear about it and the scenarios that you found this beneficial And you use it in your own work as I said at the beginning if you did enjoy this video Please do give it a like it'd be greatly appreciated and also do subscribe to the channel We've got lots of videos already available. So check those existing ones out But obviously hit that notification button as well. So you are notified as soon as our new videos come out Thank you very much for watching and we'll see you in the next video Before you go don't forget to check out the other videos on our channel You'll see everything from other functions and formulas through to tips and tricks We've also created some playlists so you can see these categorized together So make sure you check those out and get all those useful information And obviously as always don't forget to subscribe and hit that bell notification button