How to convert negative numbers in parentheses (bank-formatted text) to numbers with minus sign?

I like to find a function or a better way to convert (9,999) to -9999. This problem occurs when I use readtable(...) to read an html file that has a financial report. In it, the negative currency is encoded in bank-formatted text. For example, (1,234) means -1234.

In detectImportOptions(...) or HTMLImportOptions, there seems to be no option to set for detecting bank-formatted negative currency.

I have searched for any solution. The closest relevant information I found is the function, BankText = cur2str(Value,Digits), in the Financial Toolbox. It converts negative numbers to numbers in parenthesis, the opposite of what I want.

Any suggestion?

Answer 1

[Chunru]

xbank = [" (1,234)"  "2,345,678"  "(1,234.56)"]

xbank = 1×3 string array
    " (1,234)"    "2,345,678"    "(1,234.56)"

    
x = arrayfun(@bank2double, xbank)

x = 1×3

1.0e+06 * -0.0012 2.3457 -0.0012

function x = bank2double(s)
    s = strrep(strtrim(string(s)), ',', '');
    x = sscanf(s, '%f');
    if isempty(x)
        x = -sscanf(s, '(%f)');
    end
end

[Simon]

Thank you so much for this answer. It works. And I made a modification to handle <missing> string element.

function x = bank2double(s)
% sscanf does not supoort <missing> string element.
    s = strrep(strtrim(string(s)), ',', '');
    if ismissing(s)
        x = NaN;
    else
        x = sscanf(s, '%f');
            if isempty(x)
            x = -sscanf(s, '(%f)');
            end
    end
end

With the modification, this function can be applied to a table.

partialTable = bankTable(:,3:4); 
% say, to convert the 3rd and the 4th column

partialMatrix = partialTable.Variables; 
% get the 'matrix of values' from the table,
% because there is no tablefun(..) available to apply bank2double to a table

result = arrayfun(@bank2double, partialMatrix);

The next step after this is to put the new matrix back to the table. (I am in this step.)

Answer 2

[Walter Roberson]

Use detectImportOptions and then modify the options to set that column to character or string type. Then regexprep() the column, using {'[,\)]', '('}, {'', '-'}

Then str2double the result

That is, get rid of comma and ) characters and change any remaining ( to -

[Simon]

I see that now. It's better to let readtable( ) just read in html properly to a table first. And then in next step, let a different function to handle data conversion. The 'pipeline' way works better than 'doing all things with one function by setting options cleverly'. The later approach should be avoided but is tempting lots of time. Thanks for helping me to see this clearly.

[Walter Roberson]

By using the Prefix and Suffix options you could probably get the () stripped automatically. But that would not permit you to detect which lines had the () and so needed to be negated.

Post-processing is easier.

[Simon]

I remember seeing a Youtube video in which Brian Kernighan talks about pipeline. After gaining some experience with data wrangling, I find 'pipeline' is also a very useful concept to decide what to do with data. Post-processing, and post-post processing, ... in this way, every step solve a concrete small problem and it is easier.

Answer 3

[Stephen23]

S = ["(1,234)";"2,345,678";missing;"(1,234.56)"]

S = 4×1 string array
    "(1,234)"
    "2,345,678"
    <missing>
    "(1,234.56)"

N = str2double(strrep(strrep(S,')',''),'(','-'))

N = 4×1

1.0e+00 * -1234.00 2345678.00 NaN -1234.56

[Simon]

Thanks for your elegant solution. I actually used a similar replacing function to do this when I was working on the problem in Python. That was before I switched to Matlab.