Quadra

Connecting Technology and Business.

Hey Dollar! Can you tell me how much you are in Indian Rupees today?

Any one who deals with products and services whose prices are available only in a foreign currency has to check for the Indian Rupee equivalent from their favourite exchange rate provider in the Web. Also, buyers from online stores also face this challenge, especially, if the online store has prices posted in a foreign currency. Excel provides a tool here so that frequent checking from the web service becomes redundant. Here is how this is done...

Let us first go to the web service provider that gives us the current value of the foreign currency. I am taking an example of the www.eforexindia.com website. The site provides the latest exchange rates for the Rupee against US Dollar (USD), Euro (EUR), GB Pound (GBP), Japanese Yen (JPY) and Swiss Franc (CHF).

 

eforexindia table.png 
 
Let us copy (Ctrl+c) the website's address from the address box of the Browser. (This will make things easier for us if the website's address is long and contains many special characters like %)

 

Let us now go to the Data tab of the Excel ribbon, and click on the "From Web" option. We will then paste (Ctrl+v) the website's address on the Address box and click "Go".

We will now be shown the same page we saw on the browser. Also note there will be small arrows displayed at the top left corner of the tables available on the page. We will click on the arrow that is poining to our currency conversion table. Immediately, it becomes a tickmark.

 

eforexindia table in excel.png 
 
We will next click "Import" button at the bottom right corner of the dialogue box.

 

Now we will see a dialogue box asking us to show where we want the imported external data to be displayed. We will point to the cell where we want the table to start in the worksheet. We will also click on "Properties" and choose a refresh option.

 

External data connector.png 
 

 

Note that you can choose to refresh the data from the web everytime you open the Workbook and also to refresh the data periodically after the workbook is opened and is in use. (A shorter period can be chosen depending on how often the web service provider refreshes the value in the respective website - very useful for automatically updating shareprices that keep fluctuating within a short period of a few minutes). After checking the desired checkboxes, we will click OK.

Excel will run the query in the background and will get the data from the web (all of this indicated on the status bar of the worksheet) and will display the table with the data at the desired place.

 

 Current price.png
 

 

Since this table will always be located in the same range while refreshing the data from the web, we can make use of the values in the cells in our formulae in the worksheet elsewhere. A simple example is given in the screen clipping above.

The next time you want the current exchange rate (and the pricing of items based on this, you don't have to go to eforex (or any such service provider). Just open the excel workbook that contains this web query and Excel will get the values from the website for you.