=F3*GoogleFinance("Currency:USDGBP") Also you'll need to set the currency sign yourself, using format --> numbers --> your currency. As this method simply works on percentages. Although my USD converted to GBP, it still showed a USD sign due to how i formatted the box. So watch out for that (if that made any sense) Hope that helps ^
Nate Tate I think once you've done it a couple of times it gets easier. My key mistake was forgetting to tick the 'Merge when new form is submitted' box. drove me crazy at first. Glad you found this video helpful, do let me know if you have other things you'd like a video on.
This was helpful - thank you. What about if I'm using two different currencies, for example, sometimes I'm spending Australian Dollars, and sometimes I'm spending in PGK, and creating the columns to convert either way depending on which column I input the amount into first? Is it possible?
lets say I have a master column which is column C and then I have other columns after that, for column D I have sterling and column E, I have dollar, is it possible to have a formula that every time I enter an amount for dollar that it will auto populate the euro amount into column C?
Explanation is concise and easy to follow. However, I'm received the Error Message "When evaluating GOOGLEFINANCE, the query for the symbol: 'MXNUSD' returned no data." Is this a Google change in the formula that I'm missing?
Okay, after reading quite a few forums and watching a handful of videos, I think my problem might be a bit nuanced for the Google Finance formula. Here's what I want to accomplish: 1. I want to have a single cell automatically convert Thai Baht (THB) to US Dollar (USD). Example: I select a cell and type 45 (the amount I paid in THB, when I deselect said cell it automatically converts to $1.43. 2. I want this to be date specific. Example: Expenses that were imputed on 4/15/2018 will not automatically update to 4/16/2018 exchange rates and will remain at the rate that I recorded them at. I understand number 2 is possible, but ideally I could get both 1 and 2 working together. Is this possible?
Hi. Does the value of the conversion change whenever you view the file OR does it stick to the value when you first run the formula? Example: I made a personal savings tracker sheet this year (2017) when 1 USD=50 PHP. 10 years later, Philippine economy boomed making 1 USD= 25 PHP. If I try go back to my 2017 spreadsheet, will I see the 2017 converted value (1 USD= 50 PHP) or the 2027 one (1 USD=25 PHP)? If it's the latter, is there a way to make it stick to the value of when the formula was run first?
Better yet, is there a way to specify which date you want Google Sheets to pull up its result from. Example: I want to see the value of my money a year or 5 years earlier.
+Chickee Fuerman this is possible by using the following formula. =GoogleFinance("eurusd","price",today()-1,today()) a more detailed explanation of this can be found here : stackoverflow.com/a/20716922 hope this helps you.
Flipped Classroom Tutorials Thanks for the response. However, I tried all the variations I can think of based on the links you sent, nothing worked. I always get an #N/A as a result. Can you show me exactly how will you do it? Say we're trying to convert 1 USD to PHP using 2016/06/25 exchange rate.
Open a new sheet within your same workbook and using the following formula: =GoogleFinance("usdphp","price","25/6/2016","26/6/2016") You can call up the historical rates for 25/6/2016 You can then do what you want with that info ;-) Hope this helps.
+Guy Heretz absolutely. This is in real time with very little delay. Perfect for tracking personal finances. You can find even more info and power uses of this here: support.google.com/docs/answer/3093281?hl=en
Absolutely! your would use the CONVERT(value, start_unit, end_unit) function. More information on the use of this can be found here: support.google.com/docs/answer/6055540?hl=en Hope this answers your question.
Hi!! Thank you so much for this video, is very helpfull. But, it seems that the link with the codes no longer works :( Is there any other place we can find them? Thank you!
Hi, you would still have to reference the currency used. If you are taking it from anothet cell, then maybe you can reference that columns header which could refer to the country code. Hope this helps.
Thank you very much for this tutorial! Is it possible though to input the value at one field, and for the adjoining fields to be automatically calculated? For example, you have 3 fields (EUR, USD and THB) and when you input some number in the EUR cell, the USD and THB would be automatically calculated. Similarly, if you would input something in the USD cell, the EUR and THB values would be automatically calculated.
+AppA I am not aware of a 'Realtime' Converting formula for this sort of conversion. As the Formula works with a source Cell (Cell*GoogleFinance("CURRENCY:CURRfromCURRto")) and converts this to any other currency The best workaround would be to have double columns for each currency. For example EUROtoUSD and USDtoEURO. Lets hope they find a way around this. If anyone else has found a different formula to get this done please do let me know As I think it would be a much more usable spreadsheet that way.
+Flipped Classroom Tutorials (ICT in Education) but how working with not big price, for example : 5.89$ or 8.99$ i donw know how work with .99$ 1.21$ its "VALUED"
+Jesper Pedersen Hi, What currency are you trying to convert and what Formula are you using? You can fin the currency code here: developers.google.com/adsense/management/appendix/currencies
+Flipped Classroom Tutorials (ICT in Education) I used the same formula as you did in the video i just get #NA in my field? I was just following your videos and trying to learn Google forms.
Did you check the formula? =A5*GoogleFinance("CURRENCY:EURUSD") -->A5 should be the cell you refer to.... Also, Make sure the cell type is set to currency, not text. Hope this helps. You could always try creating a new spreadsheet following the step one by one.
Hopefully someone will be able to help me; I keep getting a #REF! error with this code. "Circular dependency detected. To resolve with iterative calculation, see File > Spreadsheet Settings." I've looked in the setting and tried turning on Iterative Calculation, but that didn't help me.
Okay. I have done this a million times now and sheets still won't convert my numbers into currency and therefore it won't sum up all my expenses. Meaning, I can use my calculator like an idiot and fill it in afterwards.
is there no way to tell it to convert to a specific date ? like 3rd June ?
Other people would explain it in a very complicated way. Yours is so simple and easy to understand. Thanks!!!!!
Thank you appreciate that
=F3*GoogleFinance("Currency:USDGBP")
Also you'll need to set the currency sign yourself, using format --> numbers --> your currency. As this method simply works on percentages. Although my USD converted to GBP, it still showed a USD sign due to how i formatted the box. So watch out for that (if that made any sense)
Hope that helps ^
Incredibly precisely stated when it is so difficult to comprehend Excel with currencies.
Glad it was helpful!
Thanks! Do you know how to stop the currency auto updating once it's been converted?
I have a question how would I do this
So what ever currency I added, it would fill in the other currencies out if the 3 cells
did you find out?
Thanks, how about one column have various currency and I would like to formatting them the same currency in another column.
FANTASTIC VIDEO THANKS MAN
Thanks
Thanks so much for the tutorial!
Glad it was helpful!
Thank you so much sir! Just used this in my budgeting spreadsheet! More power to you!
Thank you! It's great to hear you like the video
Thanks man! Super useful. Hope life in Thailand is good.
so helpful thanks sir!
Most welcome!
Thanks! This helped a lot, I'm making a massive spreadsheet and this has made it so much easier
Thank you so much for this video! is there a way to get current goldprice in a cell?
Thank you so much for making this! I've been trying to figure out how to do it for over an hour but it's working now.
Nate Tate I think once you've done it a couple of times it gets easier. My key mistake was forgetting to tick the 'Merge when new form is submitted' box. drove me crazy at first. Glad you found this video helpful, do let me know if you have other things you'd like a video on.
Much appreciative for your talent and help. Using this to make my wholesale catalogues.
Will this function update the currency exchange rates all the time or daily or will it just convert them at the time I put in a value?
Very helpful!
Glad it was helpful!
This was helpful - thank you. What about if I'm using two different currencies, for example, sometimes I'm spending Australian Dollars, and sometimes I'm spending in PGK, and creating the columns to convert either way depending on which column I input the amount into first? Is it possible?
very helpful
lets say I have a master column which is column C and then I have other columns after that, for column D I have sterling and column E, I have dollar, is it possible to have a formula that every time I enter an amount for dollar that it will auto populate the euro amount into column C?
Cheers brother
Very well explained !! Great Job !!!
Thanks a lot! Hopefully you find many more videos on the channel of use.
Hello thank you for these videos. Since I was beginner in Google docs these are so very helpful.
Glad you found it helpful!
how can i make that when i write in the column G it chages the value in column F and H ? and Vice versa
great tutorial, thanks for share
Legend. Thanks!
And so easily explained !
Thanksss! it was a short, well explain video. It worked.
Thank you
Very helpful thanks for making it easy!
Thank you. More updates videos on sheets posted so hopefully you find many helpful ones on the channel
Great Explanation, Many Thanks
Thanks for the tip! Grateful
Explanation is concise and easy to follow. However, I'm received the Error Message "When evaluating GOOGLEFINANCE, the query for the symbol: 'MXNUSD' returned no data." Is this a Google change in the formula that I'm missing?
=GOOGLEFINANCE("MXNUSD")
Okay, after reading quite a few forums and watching a handful of videos, I think my problem might be a bit nuanced for the Google Finance formula.
Here's what I want to accomplish:
1. I want to have a single cell automatically convert Thai Baht (THB) to US Dollar (USD). Example: I select a cell and type 45 (the amount I paid in THB, when I deselect said cell it automatically converts to $1.43.
2. I want this to be date specific. Example: Expenses that were imputed on 4/15/2018 will not automatically update to 4/16/2018 exchange rates and will remain at the rate that I recorded them at.
I understand number 2 is possible, but ideally I could get both 1 and 2 working together. Is this possible?
I have the same question did you get a answer if so share it with me
Very easy to understand! Thank you for the simple teaching!
Any new Google Sheet that I open has an auto-format for the Pound. I would like this to be AUS Dollar... is there any tricks for this?
Hi. Does the value of the conversion change whenever you view the file OR does it stick to the value when you first run the formula?
Example: I made a personal savings tracker sheet this year (2017) when 1 USD=50 PHP. 10 years later, Philippine economy boomed making 1 USD= 25 PHP. If I try go back to my 2017 spreadsheet, will I see the 2017 converted value (1 USD= 50 PHP) or the 2027 one (1 USD=25 PHP)?
If it's the latter, is there a way to make it stick to the value of when the formula was run first?
Better yet, is there a way to specify which date you want Google Sheets to pull up its result from. Example: I want to see the value of my money a year or 5 years earlier.
+Chickee Fuerman this is possible by using the following formula. =GoogleFinance("eurusd","price",today()-1,today()) a more detailed explanation of this can be found here : stackoverflow.com/a/20716922 hope this helps you.
+Chickee Fuerman a more complete overview of googlefinance and it's power user functionality can be found here: support.google.com/docs/answer/3093281
Flipped Classroom Tutorials Thanks for the response. However, I tried all the variations I can think of based on the links you sent, nothing worked. I always get an #N/A as a result. Can you show me exactly how will you do it? Say we're trying to convert 1 USD to PHP using 2016/06/25 exchange rate.
Open a new sheet within your same workbook and using the following formula: =GoogleFinance("usdphp","price","25/6/2016","26/6/2016") You can call up the historical rates for 25/6/2016
You can then do what you want with that info ;-) Hope this helps.
Thank you so much you just saved my life
Thank you very much, really helps
That's so good. But can I use it in Excel? I need it for my marketing work.
Hey, you can go search Vanna Excel and see if they have this tutorial.
There is: ruclips.net/video/s23a51d34Qk/видео.html
Just what I needed! Thank you!
Can you do something similar with addresses For example to see how many mins it would take to drive from depot to site and back.
Thank you, very useful
Thank you so much. It solved my problem
very professional
Learnt something thanks bro
Glad to hear it
Will this data refresh automatically? Will it be always up to date? Or should I configure it somehow?
+Guy Heretz absolutely. This is in real time with very little delay. Perfect for tracking personal finances. You can find even more info and power uses of this here: support.google.com/docs/answer/3093281?hl=en
Can you possibly do this with measurements like inches into centimeters and vice versa?
Absolutely! your would use the CONVERT(value, start_unit, end_unit) function. More information on the use of this can be found here: support.google.com/docs/answer/6055540?hl=en Hope this answers your question.
Thank you
Muchas gracias hombre, very useful
thank you bro
i am facing an error while i am trying to to convert form AED to USD can you find the reason ?
Hi!! Thank you so much for this video, is very helpfull.
But, it seems that the link with the codes no longer works :( Is there any other place we can find them?
Thank you!
Penélope Sánchez Thank you for your Comment. Try using the following link: www.science.co.il/International/Currency-codes.asp Hope that helps!
Yes! It works! Thank you very much :D
=F3*GoogleFinance("CURRENCY:USDGBP") is giving me a "Circular Dependency Detected" error
Thank you. Im in Thailand and this is what Im looking for
+Tanawat Wattanachinda Glad you found it helpful! Happy Songkran!
Very helpful! Thanks a lot!
Definitively Helpful that trick with F3*Currencyconversion :)
Helped me out a lot. Thanks.
Thank you for your feedback!
If I want to convert it on a specific date, how can I do it?
Dude, thanks a lot, you're a genius!
Thanks!
You are awesome! Thank you
Thank you very much ! have a nice day!
+AllPay You too! Thank you.
Thx! It works fine😀
Very helpful thank you!!!!
You're welcome!
Awesome. Thanks!
thank you!
You're welcome
Thanks...very helpful
You're welcome!
What if I don't know the specific currency code, because it is a variable? (I have to take it from another cell)
Hi, you would still have to reference the currency used. If you are taking it from anothet cell, then maybe you can reference that columns header which could refer to the country code. Hope this helps.
Thank you! You have saved me a lot of money and most important, TIME!
But in phone is this feature there😕
Cut to the chase:
=GOOGLEFINANCE("Currency:USDGBP")
Thank you so much for your lessons! It's really helpful!
Thank you!! Problem solved!
Thanks a lot !
You're welcome!
how can i change the currency?
Thank you very much for this tutorial!
Is it possible though to input the value at one field, and for the adjoining fields to be automatically calculated?
For example, you have 3 fields (EUR, USD and THB) and when you input some number in the EUR cell, the USD and THB would be automatically calculated. Similarly, if you would input something in the USD cell, the EUR and THB values would be automatically calculated.
+AppA I am not aware of a 'Realtime' Converting formula for this sort of conversion. As the Formula works with a source Cell (Cell*GoogleFinance("CURRENCY:CURRfromCURRto")) and converts this to any other currency The best workaround would be to have double columns for each currency. For example EUROtoUSD and USDtoEURO. Lets hope they find a way around this. If anyone else has found a different formula to get this done please do let me know As I think it would be a much more usable spreadsheet that way.
+Flipped Classroom Tutorials (ICT in Education) but how working with not big price, for example : 5.89$ or 8.99$
i donw know how work with .99$
1.21$ its "VALUED"
OMG thank you so much
+Cheong Yun Keen you're welcome!
Very helpful, thanks
You're welcome
THANK YOU! :)
its really helpful
+AzIz Baig Thank you.
Thank YOU!!!
Thanks frfr
the link for the currency codes goes to adsense?
Here´s one that works: www.iban.com/currency-codes
Great
Thanks
very helpful thanx
+Brandon Pearce You're welcome!
tHANKS
thank u
For me this does not work! have you entered any formulas to the "pre-made table" ?
+Jesper Pedersen Hi, What currency are you trying to convert and what Formula are you using? You can fin the currency code here: developers.google.com/adsense/management/appendix/currencies
+Flipped Classroom Tutorials (ICT in Education)
I used the same formula as you did in the video i just get #NA in my field? I was just following your videos and trying to learn Google forms.
Did you check the formula? =A5*GoogleFinance("CURRENCY:EURUSD")
-->A5 should be the cell you refer to....
Also, Make sure the cell type is set to currency, not text. Hope this helps. You could always try creating a new spreadsheet following the step one by one.
THANK YOU VERY MUCH!!! XXX
Great!
brilliant
Thanks!
Perfekt Thanks
Hopefully someone will be able to help me; I keep getting a #REF! error with this code.
"Circular dependency detected. To resolve with iterative calculation, see File > Spreadsheet Settings."
I've looked in the setting and tried turning on Iterative Calculation, but that didn't help me.
Nevermind, I'm dumb lol. I kept putting the empty cell as the reference cell. Duh.
lOVE YOU
I think the currency codes are no longer available.
Thank you spotting this. The updated link is here: developers.google.com/adsense/management/appendix/currencies
Thanks for the update, and for the video. Very useful.
i can use ut, ty m8
little bit hard but It is good
Okay. I have done this a million times now and sheets still won't convert my numbers into currency and therefore it won't sum up all my expenses. Meaning, I can use my calculator like an idiot and fill it in afterwards.
not working for me..
Make sure you check the current currency codes. You can find the ones you need by searching on Google. Hope it helps.
My F3* formula gives me a 0.
i used the esame method but is giving me NA. Googlefinance result is not available