Create a Currency Converter With Microsoft Excel

If you've ever needed to know how many pesos were in a dollar or how many yen equal a pound, there's a simple way to find out using Excel. To create a currency converter with Microsoft Excel, you'll need to navigate to Data > Import External Data > Import Data and select MSN MoneyCentral Investor Currency Rates from the list of possible connections. This currency converter gets exchange rates from the Internet automatically, so you should have the most updated information available.

Steps

  1. Start Microsoft Excel, and create a new workbook.
  2. Or alternatively go to http://www.tlookup.com and download the daily generated Excel file with the exchange rates of the 34 major currencies over the last 90 days.
  3. Starting in Column D, enter cell content as shown here:
  4. 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.
  5. Import the MSN MoneyCentral Investor Currency Rates file found in the My Data Sources folder as follows:
  6. Import the data from the file into a New Worksheet like so (but DON'T press "OK" yet):
  7. 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.
  8. Click OK on the Properties window and on the Import Dialog window.
  9. Once the data is imported into the new worksheet, change the name of the new worksheet to Conversion.
  10. 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.
  11. Copy all cells from A5 through to A46.
  12. Paste the cells into your original worksheet in column B as shown here:
  13. Now that we have all the currencies, we should create a drop-down 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.
  14. 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 drop-down option is checked.
  15. Repeat the previous step in cell D12.
  16. 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.
  17. 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)
  18. You're all done!
  19. You can now customise the spreadsheet to reflect the base currency of your choice.
  20. Decide on your base currency (we will use South African Rand in this example)
  21. In our example South African Rand is located in Conversion Worksheet Row 37.
  22. Select the Original Worksheet and insert an additional column between the currency names and the calculator. The calculator will now shift to Columns E & F.
  23. Highlight Column A, from the top menu click Edit>Find.
  24. Type in "to US Dollar" select the Replace Tab and click Replace All. Close the Find pop-up menu. Note that Cells F6 and F12 will change to N/A...do not worry about this...after completion of this exercise, reselect currencies in Cells E5 and E12 and all will be well.
  25. Select Cell C4 (the first Currency Row) and insert formula:

    =Conversion!B6*Conversion!C$37.
  26. Select Cell D4 and insert formula:

    =Conversion!C6*Conversion!B$37.
  27. Select Cells C4 and D4 and drag the formula down to the last currency row.
  28. Change Cell F6 formula to:

    =SUM(VLOOKUP(E5,B4:B45,2,FALSE)*F5)
  29. Change Cell F12 formula to:

    =SUM(VLOOKUP(E12,B4:B45,3,FALSE)*F11)
  30. On the currency calculator change all references to US$ to the currency you have chosen.
  31. You now have a currency calculator in a base currency of your choice. You may now proceed to format the calculator to your preference...we like to hide Columns A,B,C & D and format the calculator to our corporate colours.

Tips

  • Currency rate changes all the time, you can confirm the rates at the Currency Site http://www.xe.com/ucc/
  • 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!

Warnings

  • 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 United States Dollar 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

Related Articles

Retrieved from "https://kipkis.com/index.php?title=Create_a_Currency_Converter_With_Microsoft_Excel&oldid=273398"