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

7 views (last 30 days)
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?

Accepted Answer

Chunru
Chunru on 7 Aug 2022
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
  1 Comment
Simon
Simon on 9 Aug 2022
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.)

Sign in to comment.

More Answers (2)

Walter Roberson
Walter Roberson on 7 Aug 2022
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 -
  3 Comments
Simon
Simon on 17 Aug 2022
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.

Sign in to comment.


Stephen23
Stephen23 on 9 Aug 2022
Moved: Stephen23 on 17 Aug 2022
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
  1 Comment
Simon
Simon on 17 Aug 2022
Moved: Stephen23 on 17 Aug 2022
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.

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!