Scenario:
You have a spreadsheet that has amount in US Dollar and you need to convert it to other currency. The conversion should be done automatically whenever the workbook is open. The currency rate should be based on the latest conversion rate. As an example, let's say the conversion would be from US Dollar to Singaporean Dollar. How can we do this?
Solution:
In this situation, we are going to use a web query to capture the current currency rate in the Internet and the "open event" whenever the spreadsheet is open. In this case, we will use the Yahoo Finance Currency Conversion Calculator to provide us the most updated rate that we need.
Here are the step-by-step instructions on how to go about it:
1.) Create a new workbook and rename the first worksheet as "Amount". Rename the second as "Currency Rate".
2.) Open your browser and go to Yahoo Finance Currency Conversion Calculator by clicking this: http://finance.yahoo.com/currency
3.) In the "Currency Converter", Put 1 in the TextBox, then choose "US Dollar" in the first dropdown list and Singapore Dollar in the second one as shown in the figure below:
4.) Click the "Convert" button. Yahoo will display the currency conversion rate of the current date as shown below:
5.) Copy the URL: http://finance.yahoo.com/currency/convert?amt=1&from=USD&to=SGD&submit=Convert
6.) Going back to Excel, click the "Currency Rate" worksheet tab and place Cell A1 as the active cell. In the menu, click Data>Import External Data>New Web Query. The Excel Web Query will appear and in the "Address" field, put (paste) the URL that you just copied and click the "Go" button. See the screen shot below to know how it looks:
7.) Scroll down the window at the portion where you can see the actual conversion rate of USD to SGD. At the left side of it, you will see a small yellow square with an arrow inside pointing to the right. Click on that yellow square and it will change to green color and the arrow will be changed to a check mark as shown in the figure below:
8.) Click the "Import" button. The "Import Data" window will be displayed as shown below and then click the "Ok" button.
9.) The conversion table is now in Excel. Cell E3 has the rate from USD to SGD. Cell C3 has the date of the rate. Click now the "Amount" tab in Excel and put the following in the indicated cells:
- At Cell A1, write "USD Amount"
- At Cell B1, write "SGD Amount"
- At B2, put the following formula "=A2*'Currency Rate'!E3"
- A2 will have the USD Dollar amount to be converted. Initially, let's put 100 dollar in it.
10.) Open up the Visual Basic Editor (VBE) and write the following code in "ThisWorkBook" object:
Private Sub Workbook_Open()
Sheets("Currency Rate").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Amount").Select
End Sub
That's all it takes to do it. Whenever you open up the Excel file, it will automatically update the currency rate and eventually the Singapore Dollar amount in Cell B2 in worksheet "Amount".
Now that you know this and by using your imagination, you can play around with it and make something useful out of it. You can apply the technique to your financial or business calculation.
