excel 将Barchart.com数据抓取到Power Query时出现问题

dkqlctbz  于 2022-12-14  发布在  其他
关注(0)|答案(1)|浏览(149)

I am attempting to scrape this website into Excel's Power Query .
https://www.barchart.com/futures/quotes/CBX18/all-futures
When I attempt to pull data into Excel, Power Query cannot find the displayed Brent Crude Oil Table. The desired table displayed here...

And the result of Power query not finding table here...

I found this article which goes over digging into the HTML of the desired website through Power Query, however I wasn't able to receive my expected results.
https://datachant.com/2017/03/30/web-scraping-power-bi-excel-power-query/

Any suggestions would be greatly appreciated as I am stuck and need to figure out how to get this data into a clean Excel spreadsheet. I am open to potentially using a new source or going about the scraping in a different technique1

eqfvzcg8

eqfvzcg81#

I would recommend this website which allows 100 free monthly API calls giving you access to not just Brent but also related commodities like WTI, Ethanol and Natural gas. With a free account you can get you an average of 4 datapoints a day (since market is closed on weekends and holidays) for free at different points in time during the day this would allow you to build a far more meaningful data set.
https://commodities-api.com/
This is going to be way, way easier than trying to setup some sort of web-scraping system because the HTML of that web-site could easily change, and the model for this API has been around for quite a while.
If you need old data, there are plenty of downloadable spreadsheets but if you need free up to date data, on how commodities are moving during the day, this is definitely the way to go.
here is an example

GET https://commodities-api.com/api/latest
{
"base":USD
"date":2022-12-13
"rates": { 
     "ARABICA COFFEE": 1.65
     "ROBUSTA COFFEE": 1872
     "BRENT OIL": 80.620000000002
     "COCOA": 2546.5
     "CORN": 6.4900000000001
     "COTTON": 0.86000000000001
     "ETHANOL": 2.161
     "LUMBER": 406.5
     "NATURAL GAS": 7.0369999999999
     "RICE": 16.71
     "RUBBER": 1.49
     "SOYBEAN": 14.83
     "SUGAR": 0.1975
     "WHEAT": 325.98179999999
     "WTI OIL": 75.55
     "OAT": 341
     "COAL": 405.00000000001
     "LEAN HOG": 0.8205
     "LIVE CATTLE": 1.5498
     "CANOLA": 871.99999999999
     "GOLD": 1813.3446850696
     "SILVER": 23.790303528843
     "PLATINUM: 1034
     "PALLADIUM: 1887
     "RHODIUM: 12700
     "RUTHENIUM: 750.00000000002
   [170+ world Currencies, Commodities and Cryptocurrencies]
  },
 "unit": per barrel for Oil, per ounce for Metals. Per 10 metric tons for Crude Palm Oil, Per MMBtu for Natural gas, Per Gallon for Ethanol. Per metric ton, per lb or per bushel for Agriculture
 }

Other websites are going to continually change small things about their site to avoid scraping, using a free API is a better solution.

相关问题