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

8 次查看(过去 30 天)
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?

采纳的回答

Chunru
Chunru 2022-8-7
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 个评论
Simon
Simon 2022-8-9
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.)

请先登录,再进行评论。

更多回答(2 个)

Walter Roberson
Walter Roberson 2022-8-7
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 个评论
Simon
Simon 2022-8-17
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.

请先登录,再进行评论。


Stephen23
Stephen23 2022-8-9
Moved: Stephen23 2022-8-17
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 个评论
Simon
Simon 2022-8-17
Moved: Stephen23 2022-8-17
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.

请先登录,再进行评论。

Community Treasure Hunt

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

Start Hunting!

Translated by