Thursday, July 12, 2007

How to Create a Currency Converter With Microsoft Excel

Ever needed to know how many pesos were in a dollar? Or how many yen equal a pound? Here's a simple way to create a currency converter within Excel that gets the exchange rates from the Internet automatically!
Start Microsoft Excel, and create a new workbook.
Starting in Column D, enter cell content as shown here:

What you should have once you complete Step 2

Instead of typing all the names of the major world currencies, import them plus the current exchange rates from an External Data Source. Start by clicking Data > Import External Data > Import Data.

Importing external data

Import the MSN MoneyCentral Investor Currency Rates file found in the My Data Sources folder as follows:

Find the MSN MoneyCentral Investor Currency Rates

Import the data from the file into a New Worksheet like so (but DON'T press "OK" yet):

Importing External Data Dialog box

Before you import, click on the Properties button and make the following changes: Click the "Refresh Every..." checkbox and set it to whatever value you want, and click "Refresh on Open" which will get the new rates whenever you open the document.

Customize the properties

Click OK on the Properties window and on the Import Dialog window.
Once the data is imported into the new worksheet, change the name of the new worksheet to Conversion.
Now we have all the rates and the names of the major world currencies but to make this work, we need the names of the currencies in our first worksheet. Click on cell B5 first and then using the arrow keys on your keyboard, hit the left arrow once.
Copy all cells from A5 through to A56.
Paste the cells into your original worksheet in column B as shown here:

some of the world currencies but not all

Now that we have all the currencies, we should create a dropdown box to make sure we get the name of the currency right. Click on cell D5, and then click on the Data menu and then Validation.
In the validation screen, choose List as the type of allowable values, and the source is the range of cells that have the names of the currencies in them. Ensure that the In-Cell Dropdown option is checked.

Creating the dropdown

Repeat the previous step in cell D12.
In cell E6, enter the following formula:

=SUM(VLOOKUP( D5,Conversion! $A$5:$C$56, 2,FALSE)* E5)

This will use a lookup function that will find the matching value to whatever currency is set in cell D5, and multiply it by the quantity found in E5.

X currency in terms of a US dollar

To get what a US dollar is worth in x currency, we need to modify our formula a bit. Type

=SUM(VLOOKUP( D12,Conversion! $A$5:$C$56, 3,FALSE)* E11)

US dollar in other formats

You're all done!

In step 6, don't go below 30 minutes because quotes can be delayed 20 minutes or more due to network traffic.
In Step 8, if you click on the currency name, it will automatically open a link to MSN Money to give you additional information such as the 52 week high.
There is a limitation to this spreadsheet in that you can only go against the US dollar, but given that most world currencies are measured against it, this makes sense. Enjoy!

MSN Money and other many websites only quote wholesale exchange rates. Wholesale (also known as the "interbank" rate) is only for huge banks when they trade with one another; usually in amounts over 5 million USD minimum. Individuals won't be eligible for this rate and will get a retail rate instead. Retail is almost never as good as wholesale. To find what retail rate you'll get, you'll need to contact the institution you actually buy your currency from. Example, if you buy from Wells Fargo, call Wells Fargo and ask for their rate.
Sometimes data and the formulas won't work. Be patient!

Things You'll Need
Microsoft Excel
Internet Connection

No comments: