 Hi guys, welcome back to my YouTube channel, so for today's video I want to take a look at a cool trick hack, you could say, that I discovered recently in Google Sheets, Google Sheets being Google's online spreadsheet program, and what this is effectively is a way to bring in live currency conversion rates. What I used to do was this kind of ugly system, like kind of right here, like, you know, Euro, sorry, yeah, Euro, NAS, figure out what today's exchange rate is. Today one US dollar is worth 3.44 NAS, according to Google, so what I used to do, you know, just have this figure here, 3.44, and then multiply this cell by these cells in order to get the monthly amount of checkers, or any other time I needed to convert currencies. The problem with this system is that currency exchange rates change constantly, and certainly every day, so in order for this to be really useful, you want to be pulling in the live exchange rates, and thanks to Google's ingenuity and the Google Finance API, which can be called from Google Sheets, you're able to actually do that, and bring in live FX foreign exchange rates into your Google Sheets, very cool, very useful. So the application here is I'm basically just doing some a little bit of mid-afternoon bookkeeping for my business, and I'm just taking a look at my revenue today, comparing it to my previous year's revenues for my writing business, that's what's going on in this tab here. I'm going to do up some charting for myself in the charting tab, and in the income tab, what I want to do is I want to basically see, I'm looking at salaries here in US dollars and Euro, and seeing how much in Israeli shackles I need to be earning per month in order to sort of reach, so let's say my objective was to reach a salary this year of 50,000 USD, what I want to see is how many shackles is that per month, and it makes sense to have live exchange rates in the spreadsheet because that number is going to change as the Israeli US dollar rate and the Israeli Euro rate fluctuates. I could do Pound and Sterling as well, but I'm just going to keep it simple and do two. So the first thing I've done here now, firstly I do not claim to be a Google spreadsheet wizard, in fact I am not a spreadsheet wizard, I'm the opposite of a wizard, I'm relatively bad at spreadsheets, it's not my strong point, but you know I know enough for my own purposes let's just say, and I'm making this video really partially for my own reference so that I can look back and remember how this works exactly if it doesn't change. The formula is correct only at the time of recording this video. So what I've done here is I plotted out salaries from $20,000 through to $100,000 and I've just basically figured out what that equals per month using a simple formula A3 divided by 12 and I've just dragged that down here and that's applied that formula across this whole range. The reason I've done that is because in Israel we quote salaries on a per month basis whereas in most of the world in the US and in Europe it's per annum. So the first thing now, the reason I told you I'm not a spreadsheet wizard is I'm sure the smart thing to do would have been to integrate the division by 12 into the one formula, so divide let's say A3 by 12 and then run it through the Google finance formula. I just did it this way because these figures are going to remain constant and just to make this as easy as possible and show my step-by-step methodology here. So firstly I've broken the annual salaries in these currencies into monthly salaries that's going to be the same so I just copied and pasted that little row of cells into the euro reckoning here and the only thing that I need to do now in order to get these into shackles but to do so with live currency rates is to just use this equivalence. Now it'll be pretty easy for me to see if I'm doing this right because the shackle is a big currency in inverted commas in other words one US daughter translates to 3.44 shackles today at the time of recording and sorry one euro in shackles converts to 4.03. We're going to get numbers that are about three points you know something to four times the size of the first ones so if the numbers are bigger by that amount it's gone well if there are smaller something's gone wrong. So what I just googled here was Google chiefs currency conversion and extendoffice.com has the formula and it says basically you the formula you want to use here is take your target cell multiply it by Google finance I'm just going to go ahead and copy this okay Google finance currency and then the two currencies together source currency target currency so I'm just going to actually scoop up this bit here copy that and I'm just going to say here that C3 is going to be equal to B and I'm just going to go actually into the formula equals B sorry B3 and I've just done a paste here B3 multiplied by Google finances live race currency and it's going to be from USD to ILS is actually the official currency symbol for Israel not but NAS is just a popular a popular shorthand close that out unless he did it work loading looks good so that has worked and what I can just do then is and if I want to verify I can just quickly tot up 1,666 NAS sorry USD and NAS and that indeed equals 5,726.13 it's about the same number it's like discrepancy I guess because of the rate the Google search is giving and the rate that the API is giving but it's within two cycles which is you know within about naught point something percent so I can then just go ahead and pull this down and you can see that I have my rates for all these amounts and then I can just use this little nice rounding feature just to I just needed to the nearest shackle is more than enough so now I can see that to keep up to a target income of 50,000 US dollars I need to be earning 14,000 shackles a month give or take to repeat the process for euro I'm just going to go up here and I'm just going to copy over the formula dump the formula there except that my source currency here is going to switch to EUR for the euro hit enter it takes a second to load and that's a good sign because you know it's pulling from the API and this is just because the euro is worth a bit more than the US dollar in relation to the Israeli shackle those numbers are a bit bigger I'm going to do the same trick again just to round off those figures till I get to the nearest one shackle and I'm just going to let's say 50,000 is what I'm interested in it's not a bad figure to look at that is 14,000 shackles in dollars and 50,000 is that's actually quite a big discrepancy to keep up to a target income of 50,000 euro I would need to be earning closer to 17,000 shackles per month so that's basically it the cool thing about this is that these rates are live so if I come back into this spreadsheet into this Google sheet tomorrow those rates are going to be updated and you can actually see there's a little bar now at the bottom of my Google sheet saying quotes are not sourced from all markets and may be laid by up to 20 minutes little disclaimer here information is provided as is solely for informational purposes not for trading purposes or advice so basically those rates have been pulled into my spreadsheet they will update and that is how you can use Google finances live foreign exchange rates and use those in a Google sheet hope this has been helpful for anybody watching anybody with any questions about this or anything else just wants to get in touch please feel free to reach out to me at my website it's at Daniel Rose Hill 2000 Rose Hill dot co dot I L thanks for watching have a great day