LinkBack URL
About LinkBacks
Hi friends,
Can we write a code in VBA and get a data from a website securely? I am seeking exchange rates data from central banks website (Bank of Canada).
Thank you
Hi friends,
Can we write a code in VBA and get a data from a website securely? I am seeking exchange rates data from central banks website (Bank of Canada).
Thank you
Not sure what you mean by "securely". But, Bank of Canada provides API access to CSV, JSON & XML data.
Ex: XML
To get CSV or JSON, you replace xml with csv or json respectively.
Please Login or Register to view this content.
There's various ways to import data to Excel (VBA, WebService() function, PowerQuery etc).
See attached for sample WebService() method combined with FilterXML() for single day FX rate.
See link for their documentation on Valet API.
https://www.bankofcanada.ca/valet/docs
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
Re: Web Crawling by VBA code
Thank you CK76. Much appreciated.
Hi CK76,
Can you please explain in brief the process that you carried out to extract this information? When I copy pasted the Filter Function formula, it gave the same result. I have attached the screenshot for your reference.
Attachment 624628
Thank you
Roshan
FILTERXML returns array with multiple values. So you need to select range and then confirm using CTRL + SHIFT + ENTER.
Have a read of link for more details.
https://support.office.com/en-us/art...5-c1374812f5b7
Alternately you can use INDEX() function to return result.
Ex: In cell A4 enter below and copy down until you see #REF error. That means you've reached end of available record.
Formula:
Please Login or Register to view this content.
Thank you CK76.
You are welcome and thanks for the rep
If your question is resolved, please mark the thread as solved by using "Thread Tools" found at top of your initial post.
I am using this thread once again to get a formula or VBA code to fetch the data from the website that does not have API and requires authentication. I am trying to fetch the credit ratings data from S&P and Moody's for number of institutions without searching one by one.
URL: https://www.moodys.com/
URL: https://www.standardandpoors.com/
Credit ratings data of Amazon Inc., Google LLC etc.
For authentication, I have created a GMAIL account - [email protected] with password - developerstest1
Should you have any queries, please let me know.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks