May 15 2020 02:35 PM - edited May 15 2020 02:51 PM
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.
May 15 2020 03:21 PM
May 16 2020 02:05 AM
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=1d
For 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))*86400
Thus 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.
May 16 2020 03:40 AM - edited May 16 2020 03:42 AM
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.