How to convert negative numbers in parentheses (bank-formatted text) to numbers with minus sign?
10 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
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?
0 commentaires
Réponse acceptée
Chunru
le 7 Août 2022
xbank = [" (1,234)" "2,345,678" "(1,234.56)"]
x = arrayfun(@bank2double, xbank)
function x = bank2double(s)
s = strrep(strtrim(string(s)), ',', '');
x = sscanf(s, '%f');
if isempty(x)
x = -sscanf(s, '(%f)');
end
end
Plus de réponses (2)
Walter Roberson
le 7 Août 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 commentaires
Walter Roberson
le 9 Août 2022
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.
Voir également
Catégories
En savoir plus sur Characters and Strings dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!