title: SQL Server: Currency Conversion, Performing Calculations, and Displaying Results
body: <p>SQL Server 2008</p>
  
  <p>I am tasked with designing a modification to our existing systems. On the user interface, we will need to allow input of currency values from the user's currency types. Store the converted amounts in USD. Do calculations on the stored USD and store the results of calculations in USD. Display back to the current user in their configured currency.</p>
  
  <p>Summary of main problem
  If the user enters a value in EUR (for example) we would convert it to USD and store the value in the database (db), along with an ID linked to the conversion rate used for the calculation. When storing to the db, we would have to store about 8 decimal places in order to accurately get back to the original entered amount  in EUR, when converting back from USD, for display purposes.
  Another user can log in that is configured to display amounts in USD, and what will they see? We may be storing data down to 8 decimal places in the DB, but do we round/floor/ceil to get down to the cent?
  If we only display amounts down to the cent of the currency in both the UI and reports, then we will have balancing issues at some point, especially when displaying is USD, or converting to a currency other than the original currency.</p>
  
  <p>Detailed Description
  I have problems conceiving to what scale to save the values. Currently we only store to two decimal places, even though we are using the Money data type. If necessary, we will change from Money to Decimal(16,9) or so.
  Additionally, I have problems conceiving how to display the data back to the user since right now we only show down to the cent in the UI and reports.</p>
  
  <p>I foresee problems showing expected values after converting BACK from USD to the user's currency type. It all comes down to what we store in the back-end, and how we floor/ceil/round.</p>
  
  <p>We would store all values in a base currency which would be USD. Calculations are performed on entered values, and other data is created, and stored, from the results.</p>
  
  <p>The system would allow a user to have a setting of EUR, etc., and the amounts would be displayed in that currency and format in the user interface. Users will not be able to enter values smaller than the lowest denomination for that currency.</p>
  
  <p>Recap:
  User enters amount in EUR. System stores in USD. Modules run on USD value and create other data in USD values and stores results. At a later time/date, the user could come back to the system, pull the record, see the original amount in EUR, and see the other calculated amounts now translated to EUR. Our reports and user interface only display values down to the cent.</p>
  
  <p>Converting the calculated USD amounts to a different currency, in addition to rounding to the cent of that currency can cause balancing issues across the values displayed.</p>
  
  <pre><code>In the example below: 'a' is entered by user, 'b' is a look-up in the system, 'c' - 'd' - 'e' are all calculated and stored in database.
  
       a EUR * b conversion rate = c USD (value floored)
      c USD * &lt;formula&gt; = d result USD (value floored)
      USD - d result USD = e result USD
  
  Balance formula:
  USD = e result USD + d result USD
  
  If a user enters 120.00 EUR, we get:
  120.00 EUR * 1.330879 = 159.70548000 USD
  159.70548000 USD * 0.89 =  142.1378772000 USD
  159.70548000 USD - 142.1378772000 USD = 17.5676028000 USD
  Balance:
  159.70548000 USD  = 17.5676028000 USD + 142.1378772000 USD 
  
  Showing back as EUR:
  a: 159.70548000 USD * (1/1.330879 ) = 120 EUR
  d: 142.1378772000 USD * (1/1.330879 ) = 106.8000000000000000 EUR
  e: 17.5676028000 USD * (1/1.330879 ) = 13.2000000000000000 EUR
  Balance:
  120 EUR = 13.2000000000000000 EUR + 106.8000000000000000 EUR
  
  If a user enters 120.00 EUR, and we floor our results, we get:
  120.00 EUR * 1.330879 = 159.70 USD (value floored)
  159.70 USD * 0.89 =  142.13 USD (value floored)
  159.70 USD - 142.13 USD = 17.57 USD
  Balance:
  159.70 USD  = 17.57 USD + 142.13 USD 
  
  Showing back as EUR:
  a: 159.70 USD * (1/1.330879 ) = 119.99588242 EUR
  d: 142.13 USD * (1/1.330879 ) = 106.79408120 EUR
  e: 17.57 USD * (1/1.330879 ) = 13.20180122 EUR
  Balance:
  119.99588242 EUR = 13.20180122 EUR + 106.79408120 EUR
  If we had floored the currency conversion: 119.99 EUR = 13.20 EUR + 106.79 EUR
  Should be showing 120.00 EUR since that was what was originally entered by the user.
  
  Showing back as EUR (and rounding):
  a: 159.70 USD * (1/1.330879 ) = 120.00 EUR (Rounded)
  d: 142.13 USD * (1/1.330879 ) = 106.79 EUR (Rounded)
  e: 17.57 USD * (1/1.330879 ) = 13.20 EUR (Rounded)
  Balance:
  120.00 EUR &lt;&gt; 13.20 EUR + 106.79 EUR (actually equals 199.99)
  Off by 1 cent.
  </code></pre>