Tuesday, 11 September 2018

How to Import Live Data from a Web Page to Excel Spread Sheet

We can manually copy and paste data from a website to an excel sheet. This process is doing by simple entering each data into single cells separately. But it may be a time-consuming process. It will also make us unhappy if the nature of the data is continually changing one.

Learn Excel, Tableau, and SQL to analyze data, create forecasts and visualizations in 5 online courses from Duke. Enroll today!

Importing live data from the website to excel spreadsheet an important tool normally used by  the dealers in banks if they want to know the live exchange rates from many websites for analysis and interpretation, exchange houses and their staff to understand the competitiveness of the rate while analysing the exchange rate from many live websites, accountants or other finance professionals to get updated of live market rates and even for layman for knowing the mutual funds' fund value of many different funds in a single place etc

How to Impart live data to Excel

Suppose we need to find the live Currency Data from a website https://www.xe.com/currencycharts/ To start, first, open the excel file and go to "Data", then click "From Web". It will shows as given below. 

When we click on the "From Web", it will open a pop-up box in excel called "New Web Querry" and it shows us a webpage as shown below.

Suppose we need the information shown below from the website, copy the URL of the webpage then paste it in the address bar then click "go". Then find the table of information in the processed webpage. There you can see a small yellow arrow, click on that then click the "Import" button at the bottom.

Once you click on the "Import" button, it will show another pop up "Import Data". It will ask the place do you want to put the data, then click "Ok"

It is done ..... now the data from the website is showing in the excel sheet. This a the data of currency and the currency market is changing, we need to update the table also simultaneously.  So we need to make it as live data as the changes to this data must be reflected in this data also frequently. 

To do it, we need to go to the Properties in Data in the menu bar, then a pop up of "External Data Range Properties" will display. There we can see the "Enable Back Ground Refresh". There you can see two options, one for entering the minutes for every refresh of the data and another one for refreshing the data table whenever you open the file. Select the desired one and click "Ok".
It shows in the below picture 

Now whenever the data in the webpage is getting updated and at the same time, it will get updated in the excel spreadsheet also.

Hope this article is helping you to understand the process to import live data from a web page to excel. If it is useful to you, please do not hesitate to share with your social media and other friends also.
Qatar Airways