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

7 次查看（过去 30 天）
Simon
Simon 2022-8-7
评论： Simon 2022-8-17
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
Ran in:
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 个评论
显示 1更早的评论
Walter Roberson
Walter Roberson 2022-8-9
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
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
移动：Stephen23 2022-8-17
Ran in:
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
移动：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.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Characters and Strings 的更多信息

标签

产品


版本

R2022a

Community Treasure Hunt

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

Start Hunting!

Translated by