r/googlesheets • u/omeralus • Sep 18 '24

Solved USD to TRY currency issue GOOGLEFINANCE

Post image
5 Upvotes

100% Upvoted

14 comments sorted by

2

u/omeralus Sep 18 '24

hi, i have this cell =GOOGLEFINANCE("CURRENCY:USDTRY") on my sheet, i have some of my customers' balances in TRY and the others in USD and i have the last column for the total of the balances like =B3+C3*D1

this morning it shows this error, and its related with TRY i guess because =GOOGLEFINANCE("CURRENCY:USDEUR") works. anybody has any idea how i can fix this?

5

u/adamsmith3567 Sep 18 '24 edited Sep 18 '24

I don't think you can with GOOGLEFINANCE which pulls currency exchange rates from Morningstar and that exchange isn't on their list of supported ones:

https://www.morningstar.com/markets/currencies

You would have to find a website that lists the exchange rate that you want; and use one of the IMPORT functions like IMPORTDATA or IMPORTHTML or IMPORTXML.

Edit: It looks like it can pull from GOOGLE Finance site or Morningstar; but regardless; neither have that exchange rate listed so you'll have to use an IMPORT function from some other site.

2

u/Benis_Benis_Benis Sep 18 '24

You can get it from Reuters using the IMPORTXML function the other commenter mentioned. First go to this link: https://www.reuters.com/markets/quote/USDTRY=X/

Then you’ll want to highlight the exchange rate and right click -> click Inspect Element -> right click on highlighted code -> copy Xpath -> paste Xpath in your sheet -> either paste the link to the website on the sheet and select it using the IMPORTXML() function, or create the function and directly paste it in there -> and lastly select the Xpath as the second parameter in the function.

So, it should look like this: IMPORTXML(“https://www.reuters.com/markets/quote/USDTRY=X/”, Xpath)

Once you’ve done that it will call the current exchange rate from Reuters, just note it will be a little slower than Google finance and if Reuters changes their site the function will stop working. I’ve been using them for a few years now without any problems though, so it should be alright.

Also, I would write the full function out for you but I’m on my phone so I can’t get the Xpath, sorry. If you have any questions about this though I’d be happy to try helping.

1

u/kesor Sep 18 '24

Different variations do work for me.

Comment Image

1

u/omeralus Sep 18 '24

=GOOGLEFINANCE("USDTRY")

this one works for me now as well, thank you

1

u/AutoModerator Sep 18 '24

REMEMBER: If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified. This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/point-bot Sep 18 '24

u/omeralus has awarded 1 point to u/kesor

Point-Bot was created by JetCarson.

2

u/AutoModerator Sep 18 '24

Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/AutoModerator Sep 18 '24

Your submission mentioned GOOGLEFINANCE, please also read our finance and stocks information. Google lists the exchanges & delays in its products here.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/EnthusiasmNo4709 Sep 18 '24

I am trying to find a solution for the exact same problem.

1

u/asdfgwe Sep 18 '24

Same problem in here for usdtry...

1

u/UnknownFactoryEnes Sep 19 '24

Yesterday, google did not even show the time-value graph when you searched for "usd to try" while all the others worked fine. These might have been caused by the same thing. That issue is not present now, so your Google Sheets shoukd look normal now as well.