Rounding prices

Report · Sep 12, 2011

First posted this in the regular InDesign forum, but after some deliberation and guidance from other posters there, think this subforum is more suited for the question.

I'm currently working on a price list for our sales organisation, and they've given me prices that are formatted as shown below in the examples. But now they want to change it.

What I need to do is find two decimal numbers and round up the price, or when the decimal numbers are ",00", then I merely need to remove the ",00". I'm breaking my head over this and scripting makes my head spin.

Is there a script that can help me out with this, because I really shudder at the thought of having the redo all those prices.

Example:

€ 10.762,50

€ 11.812,50

Need to look like this;

€ 10.763

€ 11.813

Alternatively, numbers that are formatted like this,

€ 10.300,00

Need to look like this;

€ 10.300

I hope I explained my problem in a clear manner. If I haven't, please don't hesitate to let me know!

Thanks in advance, everyone.

Report · Sep 12, 2011

You could use the following script

var inputNo = "€ 11.812,50"

var PIndex = inputNo.indexOf(".")

var roundOffNo = inputNo.substring(0, PIndex + 1) + Math.round(inputNo.replace(",", ".").substring(PIndex + 1))

where "inputNo" is your original number and "roundOffNo" is the result.

Hope this solves your problem

Manan Joshi

- Efficient InDesign Solutions -

MetaDesign Solutions

http://metadesignsolutions.com/services/indesign-development.php

Report · Sep 13, 2011

Manan,

I wonder what will happen to a input like: € 999.999,50 ?
We would get € 1000.000 instead of € 1.000.000 .

So I would suggest a function that first strips out every dot and after rounding adds the sufficient number of dots like that:

function _roundPrice(foundString){
    var _Array = new Array();
    //Strip every dot from the string:
    var _string = foundString.replace(/\./g,"").replace(/,/g,".");
    //Strip the € sign from the array and round the number:
    var _newString = Math.round(_string.replace(/€\s/,"")).toString();
    
    //Push every single character to the array:
    for(var n=0;n<_newString.length;n++){
        _Array.push(_newString.charAt(n));
        };
    
    //Insert dots every 3 characters from end:
    for(var n=_Array.length-4;n>=0;n=n-3){
        _Array = _Array+".";
        };
    
    //Add the € sign plus a blank:
    return "€ "+_Array.join("");
    };

Now, as we have that function we need a method to search/replace the prices in the document.
If the prices strictly are formatted with a character style one can do it with the following code (given the applied character style is named "Price"):

var _d=app.activeDocument;
//Reset the Text search input field:
app.changeTextPreferences = app.findTextPreferences = null;
//Find all text applied with the character style named "Price":
app.findTextPreferences.appliedCharacterStyle = "Price";
//Built array of the found texts:
var _foundText = _d.findText();
//IMPORTANT: Loop from back to forth:
for(var n=_foundText.length-1;n>=0;n--){
    //Set the contents of the found texts to the returned strings of the function _roundPrice:
    _foundText.contents =  _roundPrice(_foundText.contents);
    };
//Reset the Text search input field:
app.changeTextPreferences = app.findTextPreferences = null;

BUT SEE THIS WARNING: if a single white space around the price is erroneously formatted with "Price" the function will not work right.

Another method could be a GREP search, though I'm not sure exactly what the right expression will be to get all possible instances of the prices like:

€ 1.000.000.000,56 (multiple dots)

€ 197,56 (no dots at all)

One expression would be:
€\\s+\\d+\\.?\\d+\\.?\\d+,?\\d+
which neglects cases without dots and is limited to maximum two dots:

var _d=app.activeDocument;
app.changeGrepPreferences = app.findGrepPreferences = null;
app.findGrepPreferences.findWhat = "€\\s+\\d+\\.?\\d+\\.?\\d+,?\\d+";
var _foundText = _d.findGrep();
for(var n=_foundText.length-1;n>=0;n--){
    _foundText.contents =  _roundPrice(_foundText.contents);
    };
app.changeGrepPreferences = app.findGrepPreferences = null;

So, maybe it's up to the original poster to come up with a better expression or doing two search/replace scripts with a slightly altered GREP.

Uwe

Report · Sep 13, 2011

Laubender,

My code had one glicth, also i had written it on the suppostion that the input no will have only one dot and one comma the question also intends this i think.

So my corrected code would be

var inputNo = "999.999,50"

var PIndex = inputNo.indexOf(".")

var Cindex = inputNo.indexOf(",")

var len = inputNo.substring(PIndex + 1, Cindex).length;

var lenAfterRounding = String(Math.round(inputNo.replace(",", ".").substring(PIndex + 1))).length;

var roundOffNo;

if(lenAfterRounding > len)

roundOffNo = inputNo.substring(0, PIndex ) * 1 + 1;

else

roundOffNo = inputNo.substring(0, PIndex + 1) + Math.round(inputNo.replace(",", ".").substring(PIndex + 1))

In this snippet i have handled the situation when the input no is something like 999.999,50 where rounding off would also effect the number before the decimal.

I have added 1 to the number before decimal in case the rounding of the number after decimal causes a carry over.

Manan Joshi

- Efficient InDesign Solutions -

MetaDesign Solutions

http://metadesignsolutions.com/services/indesign-development.php

Report · Sep 14, 2011

Manan,

thank you for your new code.
However, if I take your example of:

var inputNo = "999.999,50";

I will get a result by:

$.writeln(roundOffNo);

in the console of the ESTK of:

and not of

1.000.000

Everything goes right for numbers in the range of 0,00 to 999,49.

Uwe

Report · Sep 14, 2011

Ok, i thought the user was using a custom number format in which we could have only a single point and comma. I was not aware of the numbering system using . and , together. I suppose . here is used to seperate thousands and , as a decimal point. If this is the case then my code won't work as pointed out by you correctly.

I apolozise for the misunderstanding.

Manan Joshi

www.metadesignsolutions.com

Report · Sep 14, 2011

Thanks everyone, for the massive amounts of response.

I'm going to give all those scripts a go and let you know the results.

Report · Sep 14, 2011

This function rounds up numbers with decimal commas and dots for thousands separators:


function roundPrice (num)
    {
    // Remove thousand separators
    num = num.replace (/\./g, "");

    // Replace decimal comma with a dot
    num = num.replace (/,(?=\d\d$)/, ".");
    // Round the number up to the nearest integer
    num = Math.round (Number (num));

    // Restore thousand separators
    num = String(num).replace(/(\d)(?=(\d\d\d)+$)/g, "$1.");
    return num;
    }

Uwe -- I think this grep finds numbers following Euro symbols:

(?<=€\s)[.\d]+,\d\d

Your expression allows some flexibility in the number of spaces between the Euro symbol and the first digit, but on the other hand, you could say that a finished text should have just one space there. And with the lookbehind you match just the number, which makes processing the money amounts a bit easier.

Peter

Report · Sep 14, 2011

Not a single double space in the document, thanks to the wonders of Grep.

Thanks, I will try this out.

Report · Sep 14, 2011

@Peter:

Thank you for commenting!
And, of course, your roundPrice() function.

@Jaime:
it could have been another white space character like an En Dash, a Tab or something:
\s does find them all.

So try it out and let us know how it works…

Uwe

Report · Sep 14, 2011

Thank you, Peter.

Lau, Peter;

Struggling to combine both scripts. It now looks like this;

function roundPrice (num)
    {
    // Remove thousand separators
    num = num.replace (/\./g, "");


    // Replace decimal comma with a dot
    num = num.replace (/,(?=\d\d$)/, ".");

    // Round the number up to the nearest integer
    num = Math.round (Number (num));

    // Restore thousand separators
    num = String(num).replace(/(\d)(?=(\d\d\d)+$)/g, "$1.");
    return num;
    }

var _d=app.activeDocument;
//Reset the Text search input field:
app.changeTextPreferences = app.findTextPreferences = null;
 
//Find all text applied with the character style named "Price":
app.findTextPreferences.appliedCharacterStyle = "Price";
//Built array of the found texts:
var _foundText = _d.findText();
 
//IMPORTANT: Loop from back to forth:
for(var n=_foundText.length-1;n>=0;n--){
    //Set the contents of the found texts to the returned strings of the function _roundPrice:
    _foundText.contents =  _roundPrice(_foundText.contents);
    };
//Reset the Text search input field:
app.changeTextPreferences = app.findTextPreferences = null;

Yet it generates the following error. I'm confident that your scripting works and that the error lies with my implementation.

(fictional prices)

Report · Sep 14, 2011

You need to change two things:

1. The function's name is roundPrice but you call it as _roundPrice.
2. Use a GREP search, not a Text search:

app.findGrepPreferences = null;
app.findGrepPreferences.findWhat = "(?<=€\\s)[.\\d]+,\\d\\d";

and change other occurrences of findText with findGrep

Peter

Report · Sep 14, 2011

Peter wrote:

2. Use a GREP search, not a Text search:

Hm, in that case he will not find instances where the "€ " is missing…

He'd better search a second time for the applied character style using a GREP and adding the € plus the blank to the found strings where needed.

Uwe

Report · Sep 14, 2011

You're quite right. I hadn't realised that the prices are in a character style.

Peter

Report · Sep 14, 2011

function _roundrechtslijnend(foundString){
    var _Array = new Array();
    //Strip every dot from the string:
    var _string = foundString.replace(/\./g,"").replace(/,/g,".");
    //Strip the € sign from the array and round the number:
    var _newString = Math.round(_string.replace(/€\s/,"")).toString();
    
    //Push every single character to the array:
    for(var n=0;n<_newString.length;n++){
        _Array.push(_newString.charAt(n));
        };
    
    //Insert dots every 3 characters from end:
    for(var n=_Array.length-4;n>=0;n=n-3){
        _Array = _Array+".";
        };
    
    //Add the € sign plus a blank:
    return "€ "+_Array.join("");
    };

var _d=app.activeDocument;
//Reset the Text search input field:
app.changeTextPreferences = app.findTextPreferences = null;
 
//Find all text applied with the character style named "rechtslijnend":
app.findTextPreferences.appliedCharacterStyle = "rechtslijnend";
//Built array of the found texts:
var _foundText = _d.findText();
 
//IMPORTANT: Loop from back to forth:
for(var n=_foundText.length-1;n>=0;n--){
    //Set the contents of the found texts to the returned strings of the function _roundrechtslijnend:
    _foundText.contents =  _roundrechtslijnend(_foundText.contents);
    };
//Reset the Text search input field:
app.changeTextPreferences = app.findTextPreferences = null;

This script works. Infact, in my tests it even adds Euro signs to prices that did not have any yet! The only thing that might cause a problem for the sales people, is that it rounds both up and down. I think that they, them being sales guys, might opt for the round up only version.

Can that be specified with? (going out on a limb here);

Math.roundUP

Report · Sep 14, 2011

Jaime de Vos wrote:

[..] The only thing that might cause a problem for the sales people, is that it rounds both up and down. I think that they, them being sales guys, might opt for the round up only version.

Can that be specified with? (going out on a limb here);
Math.roundUP
 

Only FYI ('cause you seem to be enjoying this newly discovered "scripting" 😞

number ceil (x: number)
Rounds the number up to the nearest integer.

Parameter	Type	Description
x	number	A number.

Example

Math.ceil(x)

number floor (x: number)
Rounds a number down to the nearest integer.

Parameter	Type	Description
x	number	A number.

Example

Math.floor(x)

Warning:

ceil will always round up, even for the tiniest fraction above a whole number!

x = 1.000000001;

alert (Math.ceil(x));

Report · Sep 15, 2011

And Math.round(n) rounds up from .5 (3.5 > 4) down up to .5 (3.49 > 3).

Report · Sep 15, 2011

Will take some time for me to digest all the new input. Need to wrap my head around the concept some more. Compare it to following a discussion that's partly spoken in a different language. But I'm confident I can condense the proposed changes into a working script.

Thanks again.