Excel and the Web

Typically we would use Excel for our data, records, and also calculations, and right now I have an excel sheet which requires me to submit my Total Sales in Ringgit (RM) and also in USD. Before anything, let's format the cells into Currency with 2 decimal places, how I did that was just by highlighting the two cells and right-click followed by the option 'Format Cells'. In the Number tab, make the proper formatting.

I went on the internet and browsed for a site for me to refer the currency exchange rates to convert my RM to USD, and I found one on MSN. Now I am going to copy the URL of the site.

Returning to my excel worksheet from the browser, I will now refer to that currency exchange site in my RM to USD conversion formula. In excel, go to the Data tab, and in the Get External Data group, click on the feature called 'From Web' and this will actually launch a browser-like window from within excel. Paste the copied URL into the Address box in the excel browser and 'Go'.

The currency exchange website will now be loaded into the excel browser, the only difference between this and what we saw in the internet browser is that here there are tiny yellow boxes with arrows in them for us to specify the areas that we want to import into our excel sheet.

I'm going to select this Currency Rates table here by clicking on the yellow box and then hit the 'Import' button at the bottom.

After this, just specify the area to place imported data, for this I am going to select the current sheet, just below my Total Sales value. You can choose your own settings for the imported data, to do this click on the Properties button.

You will see a window like this where you can select the suitable 'Refresh Control' (for instance) for that data that you're importing from the web. Everytime the data changes in the browser, it will be reflected in your excel sheet.



Click 'Ok' and you will have the same currency rates table placed in your excel sheet at the location that you have specified. Here I have right-clicked and when you choose the 'Data Range Properties' you will see the same settings when you hit the 'Properties' button previously. 'Refresh' there indicates that the data is live/linked to the web.

I shall refer to the value here and use it in my conversion formula and not have worries of inaccuracy or doing repeatitive formula updates.

Oh yea.... CHEERS to an exciting 2010 for all.... especially with the launch of Office 2010 coming soon :)