importing from xml to Google Sheets using importxml - Stack Overflow

admin管理员组

文章数量:1344319

I would like to import into Google Sheets the ECB official USD/EUR conversion rates from this link:

.xml

But I can't figure out how to phrase the XPATH. I tried this for example:

=importxml(".xml", "/CompactData/DataSet/Series/Obs/@TIME_PERIOD")

But I always get "import content is empty". Would appreciate any help, many thanks ahead!

I would like to import into Google Sheets the ECB official USD/EUR conversion rates from this link:

https://www.ecb.europa.eu/stats/policy_and_exchange_rates/euro_reference_exchange_rates/html/usd.xml

But I can't figure out how to phrase the XPATH. I tried this for example:

=importxml("https://www.ecb.europa.eu/stats/policy_and_exchange_rates/euro_reference_exchange_rates/html/usd.xml", "/CompactData/DataSet/Series/Obs/@TIME_PERIOD")

But I always get "import content is empty". Would appreciate any help, many thanks ahead!

• xml
• google-sheets
• google-sheets-formula

Share Improve this question Follow asked yesterday Breslau70Breslau70 344 bronze badges New contributor Breslau70 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. 5

• I cannot be sure if you have the correct xpath, however your main issue here is the url you are trying to use is not a hosted xml file but rather just a download link or export link this is the reason there is nothing being imported based on your XPATH. Moreover, I do think that there are other ways to accomplish what you want if you can be more clear about what you are trying to extract. – Babanana Commented yesterday
• Basically a list of dates + number (conversion rate) for each date. They are in the XML file under "/CompactData/DataSet/Series/" – Breslau70 Commented yesterday
• I understand these parts based on the XPATH, what I am trying to say is there is any specific date range you want to get within the last 4 months? or do you really need data from the year 1999? – Babanana Commented yesterday
• No but I would need the last four years or so. The idea is to have Google Sheets calculate automatically my EUR gain/loss on investments made in USD and for that the law requires me to use the official ECB rates for each transaction... – Breslau70 Commented yesterday
• Please provide enough code so others can better understand or reproduce the problem. – Community Bot Commented 14 hours ago

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 2

Although I'm not sure whether I could correctly understand your expected values, how about the following formula?

Sample formula:

=LET(VALUE_URL,"https://www.ecb.europa.eu/stats/policy_and_exchange_rates/euro_reference_exchange_rates/html/usd.xml",BASE_XPATH,"//*[local-name()='Obs']/",{IMPORTXML(VALUE_URL, BASE_XPATH&"@TIME_PERIOD"),IMPORTXML(VALUE_URL, BASE_XPATH&"@OBS_VALUE")})

• In this case, the XPath of each value is //*[local-name()='Obs']/@TIME_PERIOD and //*[local-name()='Obs']/@OBS_VALUE.

• For example, when you want to retrieve the values of @TIME_PERIOD, you can use the following formula.

  =IMPORTXML("https://www.ecb.europa.eu/stats/policy_and_exchange_rates/euro_reference_exchange_rates/html/usd.xml","//*[local-name()='Obs']/@TIME_PERIOD")
  

Testing:

If the data that you're trying to scrape normally appears in a table on that site, as it appears it does, then using IMPORTHTML is probably the better function to use.

Try this formula:

=let(a,importhtml("https://www.ecb.europa.eu/stats/policy_and_exchange_rates/euro_reference_exchange_rates/html/index.en.html","table",1,"en_US"),chooserows(a,sequence(rows(a)-1,1,2)))

When I first tried it with the IMPORTHTML portion, it got hung up showing this error message.

It wasn't until I tried using IMPORTDATA that I got the banner asking for permission to send and receive data to external parties. As soon as I granted permission, the data came instantly, although the header row was shifted one column. Hence the reason for returning everything but the first row.

I think google sheets can open Excel. Here is a solution with python:

import pandas as pd

df = pd.read_xml("usd.xml", xpath=".//doc:Obs", namespaces = {"doc": "http://www.ecb.europa.eu/vocabulary/stats/exr/1"})
df.to_excel("currency.xlsx")

本文标签： importing from xml to Google Sheets using importxmlStack Overflow