Excel file currency conversion program for money exchange spot?
Posted on Jul 06, 2010 under Foreign Currency Conversion |I need a simple excel file which can calculate from one currency to another. It should have big buttons with currency picture, to select which currency is traded, an input field of howmuch foreign currency I receive and an output field which tells me howmuch to give the customer back. Besides that I must be able to input daily exchange rates for all currency’s. An additional plus would be a print receipt option for customer and database option to store all days transaction.
The biggest problem for me is to make buttons and to link it and make it work.
Thanks
Here’s what I would do: Firstly, I would find a reliable place to get a simple-ish list of exchange rates. Then I would highlight and copy it all to a sheet in Excel, by default Excel files have a Sheet1, Sheet2 and Sheet3. I’d copy it to Sheet1 and rename the sheet Rates. It’ll look ugly and weird, but it’s important not to fix it up.
Then on Sheet2 I would design the interface you described. You’ll need the Insert menu (on Excel 2003 or before, not sure where it is in the stupid Ribbon in crappy Excel 2007 and later) for those elements, like the buttons and the picture. And I would use a Combo Box to pick the currency/country. It takes more setting up, but it’s more natural for people. I would suggest that for the Input and Output fields, you just use the normal Excel cells, it’s the easiest. You can make all the lines on the sheet white to hide them, except for these two cells, to make them stand out and obvious. You can also use the Sheet Protection feature to make sure people can’t type anywhere except the Input cell (this includes the Output cell, so people can’t accidentally type over the formula in there).
The Output cell will have a formula along the lines of:
=C10 * (VLOOKUP(D10, Rates!$A$2:$C$20, 3, FALSE))
…Where C10 is the Input cell, D10 is the chosen country’s code/whatever, Rates is the sheet with the rates on it, A2 is the top left cell of the rates table, C20 is the bottom right cell of the rates table (the dollar signs mean that if you copy this formula, it won’t adjust these values), 3 is the column to return - Column C in this example - and FALSE means that you want EXACT matches, which will make this work even if the countries aren’t in alphabetical order. So this command means to go to Rates, find the row with the country code in the first column, then grab the value in Column C on that row, and multiply it by C10
Finally, I would set up Sheet3 as the receipt, grabbing the values from Sheet2 and putting them in a nice way, properly formatted and such. I would set the Print Area accordingly, then I would make a button on Sheet2 to just print Sheet3.
Doing it this way, it’s a simple copy and paste on Rates (a.k.a. Sheet1) to update the rates. If you had cleaned it up, you’d ALWAYS have to clean it up to make sure things are how they need to be to work.
This IS a bunch of setup work, but once done you just have to copy and paste today’s rates, and the rest works simply. Just enter the number, pick the country, click the Print button.
Mail this post
July 5th, 2010 at 11:51 pm
Hi Friend,Converting currency with Microsoft Excel is not as difficult as you might think. You can convert any type of currency in a few simple steps and one simple formula.
Please try the following steps, you may get solution.
Step 1:
Select the currency you want to convert to.
Step 2:
Find the current conversion rate. Sites such as Yahoo’s currency converter will list the latest currency conversion rate for the various international currencies. Several sites offer this service, so perform an Internet search for the site you feel most comfortable with.
Step 3:
Open Microsoft Excel. Label the first three columns in the Excel spreadsheet as "Current Currency," "Currency Conversion Factor" and "Converted Currency." You don’t have to label the columns as indicated here, but for instructional purposes, it makes it easier to identify the column’s contents. You can label them A, B and C if you’d like.
Step 4:
Insert the amount of the currency you wish to convert in the first column (Current Currency). If you’re converting $453 US dollars into another currency, insert that amount in the first column.
Step 5
Place the currency conversion factor in the second column.
Step 6
Create a formula in the third column. Insert this formula in the third column "+column1/cell1 * +colum2/cell1." In this example, assuming you did not skip a row after your title, your dollar amount will be in A2 and your conversion amount will be in B2; therefore, your formula will read "+A2*+B2." Be sure to hit the "Enter" key once you’ve put in the formula. It will automatically convert the currency.
Step 7
Add formatting. If you want your converter to look nice, format the column by bolding, underlining or adding color.
References :
http://www.ehow.com/how_2238845_create-currency-converter-microsoft-excel.html
OR
http://www.ehow.com/how_5163006_use-excel-currency-conversion.html
July 6th, 2010 at 12:15 am
Here’s what I would do: Firstly, I would find a reliable place to get a simple-ish list of exchange rates. Then I would highlight and copy it all to a sheet in Excel, by default Excel files have a Sheet1, Sheet2 and Sheet3. I’d copy it to Sheet1 and rename the sheet Rates. It’ll look ugly and weird, but it’s important not to fix it up.
Then on Sheet2 I would design the interface you described. You’ll need the Insert menu (on Excel 2003 or before, not sure where it is in the stupid Ribbon in crappy Excel 2007 and later) for those elements, like the buttons and the picture. And I would use a Combo Box to pick the currency/country. It takes more setting up, but it’s more natural for people. I would suggest that for the Input and Output fields, you just use the normal Excel cells, it’s the easiest. You can make all the lines on the sheet white to hide them, except for these two cells, to make them stand out and obvious. You can also use the Sheet Protection feature to make sure people can’t type anywhere except the Input cell (this includes the Output cell, so people can’t accidentally type over the formula in there).
The Output cell will have a formula along the lines of:
=C10 * (VLOOKUP(D10, Rates!$A$2:$C$20, 3, FALSE))
…Where C10 is the Input cell, D10 is the chosen country’s code/whatever, Rates is the sheet with the rates on it, A2 is the top left cell of the rates table, C20 is the bottom right cell of the rates table (the dollar signs mean that if you copy this formula, it won’t adjust these values), 3 is the column to return - Column C in this example - and FALSE means that you want EXACT matches, which will make this work even if the countries aren’t in alphabetical order. So this command means to go to Rates, find the row with the country code in the first column, then grab the value in Column C on that row, and multiply it by C10
Finally, I would set up Sheet3 as the receipt, grabbing the values from Sheet2 and putting them in a nice way, properly formatted and such. I would set the Print Area accordingly, then I would make a button on Sheet2 to just print Sheet3.
Doing it this way, it’s a simple copy and paste on Rates (a.k.a. Sheet1) to update the rates. If you had cleaned it up, you’d ALWAYS have to clean it up to make sure things are how they need to be to work.
This IS a bunch of setup work, but once done you just have to copy and paste today’s rates, and the rest works simply. Just enter the number, pick the country, click the Print button.
References :
Countless years as an Excel user and tutor at university.