google-sheetsgoogle-sheets-formula

google sheets IMPORTXML for currency prices


I want to retrieve a cell from website https://www.tcmb.gov.tr/kurlar/201511/30112015.xml in a google sheet by using IMPORTXML.

'Döviz Satış Forex Selling' column of 'USD/TRY' row, the value is 2.9161.

I tried 2 methods but it did not worked.

=IMPORTXML("https://www.tcmb.gov.tr/kurlar/201511/30112015.xml","/Currency CrossOrder[0]/ForexSelling")

=IMPORTXML("https://www.tcmb.gov.tr/kurlar/201511/30112015.xml","/html/body/div/div/table[1]/tbody/tr[2]/td[5]")

for the second one I used inspect and copy full xpath by selecting 2.9161

expecting the value 2.9161


Solution

  • In your situation, how about the following XPath?

    //*[local-name()='Currency'][@CrossOrder='0']/ForexSelling
    

    When this is reflected in IMPORTXML, it becomes as follows.

    =IMPORTXML("https://www.tcmb.gov.tr/kurlar/201511/30112015.xml","//*[local-name()='Currency'][@CrossOrder='0']/ForexSelling")
    

    When this is used, the following result is obtained.

    enter image description here