Forum Discussion
Getting data from web
I am trying to import some historical data for currency exchange rates on yahoo finance using the 'From Web' function in excel. The problem is that on yahoo finance, you have to scroll to the bottom of the page which then automatically loads more data and makes the page longer. However excel is only taking the data as is initially shown on the web page (without scrolling down).
Is it possible to make excel scroll down as far as possible so that it will automatically load and see all of the data?
Thanks.
4 Replies
- ok. Ejoy your weekend
Thank you for your response guys.
I figured that the best solution was to use macros and source the data from the download link they provide on the website. Its seeming relatively complex to source the data from the web page itself.
Thanks again for your help.
I'm not familiar with Yahoo Finance, but what I see historical exchange rate is set by parameters and these parameters are in URL as
https://finance.yahoo.com/quote/EURUSD%3DX/history?period1=1526428800&period2=1589587200&interval=1d&filter=history&frequency=1dFor the start/end dates Yahoo uses UNIX timestamp format. Excel date, e.g. today, could be converted to it as
UnixTime = (TODAY() - DATE(1970,1,1))*86400Thus you may set start/end dates in the Excel sheet, query them and use as parameters for the web query.
If data on web site doesn't depend on URL parameters there is no direct way to query such data. Workaround exists but not easy one.
- I think Excel imports tables from websites, so if the information is in the table, I guess it should work.
You might need to check if there are multiple tables when importing and ensure you choose the appropriate table
