Get web data into a cell

[Gregg Wolin]

I am building a simple model that requires the user to periodically input the current Prime lending rate (or other, individual rates). I'd like to have a cell that fetches the rate from the web when the workbook is open. After 30 minutes on "the Google", I decided to hang my head in shame and ask the brain trust here;-)

Thanks in advance!

 

[Marc L]

Since Excel 2013 there are web services to see in Excel help
or on web tutorials …

In earlier version you can use in Data menu From Web
but it also depends on website …

A must read : Web Scraping tutorial …
​

 

[Chihiro]

Depending on your Excel version (2013 or later). You can use WEBSERVICE function. It returns .responseText from URL.

Example usage:
=MID(WEBSERVICE("http://www.rbcroyalbank.com/personal-loans/_assets-custom/pop-up/def-primerate.html"),FIND("Rate:",WEBSERVICE("http://www.rbcroyalbank.com/personal-loans/_assets-custom/pop-up/def-primerate.html"))+6,6)

This will return current Prime Lending Rate from RBC site.

 

[Gregg Wolin]

This is great lead Chiriro, but it returns the Canadian prime rate (2.70%). The current US Fed rate is 3.75%.

 

[Chihiro]

It was just a sample usage

You will need to find a site that has Prime lending rate for U.S. and use it's URL and position of rate within the returned string.

 

[Chihiro]

Here, see another sample using www.fedprimerate.com

B1 is the cell used to contain URL source.
B2 returns current rate.

Just for fun, added B3. Which returns string of text detailing when the last change took place.