Doing calculations on values input in USD currency format (e.g., $1,234.56)

Question

I'd like to be able to copy and paste, into MMA, numbers that have a standard USD currency format, i.e., that include dollar signs and comma separators, and get the output in the same format. I'd also like this to be done automatically, without the need for a formula, which I assume means making use of one or more $Pre-type statements.

For instance, I'd like to be able to paste the following into an input cell:

$3,342.56 + $1000 + 100.5 + $45.35*1.57

and get:

$4,514.26

[The following is an edit from earlier version of this post, which used $Pre.] I can create a formula that provides the output in currency format, except that it doesn't pad to two decimals if the input number ends in the tenths place:

$PrePrint = 
    AccountingForm[N[#, 30], {Infinity, 2}, DigitBlock -> 3, 
    NumberSigns -> {"-$", "$"}] &;
4514.26
358.3

$4,514.26

$358.3

But, on the input side, I haven't figured out how to create a function that can strip dollar signs and commas from an expression. I can only get it to work if I first manually convert the expression to a string by wrapping it in quotes:

$Pre = ToExpression@StringReplace[ToString[#], {"," -> "", "$" -> ""}] &;
$PrePrint = 
  AccountingForm[N[#, 30], {Infinity, 2}, DigitBlock -> 3, 
  NumberSigns -> {"-$", "$"}] &;
"$3,342.56 + $1000 + 100.5 + $45.35*1.57"
% + 1000
%% + "$1,000"

$4,514.26

$5,514.26

$5,514.26

In fact, if I have to wrap the input in quotes anyways, I can accomplish the above using only $PrePrint; this gives the same output as the above code:

$PrePrint = 
    AccountingForm[
    N[ToExpression@StringReplace[ToString@#, {"," -> "", "$" -> ""}],
    30], {Infinity, 2}, DigitBlock -> 3, 
    NumberSigns -> {"-$", "$"}] &;

An explanation of what's motivating this

I get emails in which we're going back and forth discussing funding for grant proposals, and I'll need to run some calculations and reply. To do this, I'll cut and paste the numbers in the email into Mathematica, manually strip out the dollar signs and commas, do the calculation, paste the result back into my reply email, and add the dollars and commas back. Since sometimes there are many of these, it would be a nice convenience if I could avoid the two manual steps, i.e., avoid having to strip out the dollar signs and commas before doing the calculation, and having to add them back after pasting the result in the email.

Ultimately, when I enter $3,342.56 + $1000 + 100.5 + $45.35*1.57, I'd like to combine the answer to this with other code I already have such that MMA outputs:

$3,342.56 + $1000 + 100.5 + $45.35*1.57 = $4,514.26.

Then I can just paste the whole thing back into my email.

Answer 1

Stylesheet Approach

Easier than messing with $Pre is to never let the string get converted to boxes in the first place. We can do that by making a special CellStyle that inherits from "Text" but is Evaluatable and has as its CellEvaluationFunction the necessary code to interpret these inputs.

By evaluating this in a Notebook you can make such a CellStyle appear and be bound to Command-8

SetOptions[
 EvaluationNotebook[],
 StyleDefinitions ->
  Notebook[{
    Cell[StyleData[StyleDefinitions -> "Default.nb"]],
    Cell[StyleData["Code"], MenuCommandKey -> None],
    Cell[
     StyleData["USDInput", StyleDefinitions -> StyleData["Text"]],
     MenuCommandKey -> "8",
     FontFamily -> "Source Code Pro",
     Evaluatable -> True,
     CellEvaluationFunction -> Function[
       canonicalizeUSD[string_] :=
        StringReplace[
         StringDelete[string, ","],
         {
          "$" ~~ n : NumberString :> "Quantity[" <> n <> ", \"USDollars\"]"
          }
         ];
       calcUSD[string_] :=
        canonicalizeUSD[string] // ToExpression;
       calcUSDSloppy[string_] :=
        Quantity[
         calcUSD[string] /. q_Quantity :> QuantityMagnitude[q],
         "USDollars"
         ];
       formatUSDNumber[q_] :=
        Quantity[
         NumberForm[q // QuantityMagnitude,
          {RealDigits[QuantityMagnitude@q][[2]] + 2, 2},
          DigitBlock -> 3,  NumberSeparator -> ","],
         "USDollars"
         ];
       formatUSDNumber@calcUSDSloppy[#]
       ]
     ]
    }
   ]
 ]

This then works like a free-form input cell, not string quotes necessary

Easiest Form

Here's my suggestion from the comments. Notice how easy and safe it is

$USD /: Times[n_, $USD] := Quantity[n, "USDollars"];
$USD /: Times[$USD, n_] := Quantity[n, "USDollars"];
3342.56 $USD + 1000 $USD + 100.5 $USD + 45.35 $USD*1.57
Quantity[4514.2595, "USDollars"]

It...just works. And it uses all of the built-in Quantity machinery so you don't have to do any work yourself.

String-Based Approach

If you're copy-pasting these strings, I think this is the cleanest approach. First you use proper string-ops on the strings themselves to canonicalize the string and then you let Mathematica do the rest:

canonicalizeUSD[string_] :=
  StringReplace[
   StringDelete[string, ","],
   {
    "$" ~~ n : NumberString :> "Quantity[" <> n <> ", \"USDollars\"]"
    }
   ];
calcUSD[string_] :=
 canonicalizeUSD[string] // ToExpression

This is still easy to copy, paste and run

"$3,342.56 + $1000 + $100.5 + $45.35*1.57" // calcUSD
(* $ 4514.26  (copied using Command-Shift-C which is the short-cut for copy as plaintext)*) 

If you don't have the proper units on the strings obviously that will break things

"$3,342.56 + 1000 + 100.5 + $45.35*1.57" // calcUSD
1100.5 + Quantity[3413.7595, "USDollars"]

But you can fix this like by adding a special case for less-pristine inputs

calcUSDSloppy[string_] :=
 Quantity[
  calcUSD[string] /. q_Quantity :> QuantityMagnitude[q],
  "USDollars"
  ]
"$3,342.56 + 1000 + 100.5 + $45.35*1.57" // calcUSDSloppy
Quantity[4514.2595, "USDollars"]

Or you can strip the Quantity altogether at the end with QuantityMagnitude if all you want is the value.

If you want specific formatting, that's not hard to get

formatUSDNumber[q_] :=
 Quantity[
  NumberForm[q // QuantityMagnitude,
   {RealDigits[QuantityMagnitude@q][[2]] + 2, 2},
   DigitBlock -> 3,  NumberSeparator -> ","],
  "USDollars"
  ]
"$3,342.56 + 1000 + 100.5 + $45.35*1.57" // calcUSDSloppy // formatUSDNumber
(* $ 4,514.26 *)

Finally, worth mentioning that if you have mixed-units (say both Canadian and US dollars) the previous approach is cleaner, even if it means you need to do a minor amount of preprocessing of the input.

Free-Form Input Approach

You can also use a free-form cell which basically asks W|A to do it's free-form input cleaning. You can get one of those with = at the start of an "Input" cell