Parsing of financial data file that is very irregular in format

Question

Mark Smith 2020-8-1

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/573880-parsing-of-financial-data-file-that-is-very-irregular-in-format

评论： Mark Smith 2020-8-8

Thanks in advance for any responses. I am a retired engineer with some programming experience and am working to use Matlab for portfolio analysis. I am currently at the point of reading data from my investment company's data files. I can obtain data on my current holdings as four CSV files that are of similar but different formats. Ther are intended to be printed as PDFs I think.

Numeric values sometimes have $ symbols and sometimes have commas and sometimes have other formats. I have written some code that is successful in extracting Date, Symbol, Description, Quantity and Price from each of the files that is based on counting commas and performing IF tests. There is more information in which I may be interested but for which I have not written code. I have attached the code file which works for the full files and a sample of two files that differ in format but which have personal data removed and most of the data removed. I have not converted the attached DOCX files back to CSV to try my code on them.

I feel that what is shown in "importMLdata.m" is a brute force approach and am looking for more elegant methods for attacking these files.

What I would like in responses is direction to advanced file and string parsing methods. If the use of nested IF statements as I have used is the best approach, I would appreciate affirmation of that also so I do not think Matlab is hiding some strength from me in its documentation.

I appreciate your indulgence in evaluating this general question. The attached matlab code is a work in progress as shown by the many commented out lines but it currently does work for multiple files in a directory. I would also appreciate your comments on programming style, and any other issues you see in the code.

Thank you

7 个评论
显示 5更早的评论隐藏 5更早的评论

dpb 2020-8-2

在 MATLAB Online 中打开

They didn't do you any favors in thinking about writing out a format to parse automagically, for certain...if were doing that, they would forget about trying to mimic the format of the printed statement and write the pertinent data in a database-style format. Instead, they took the easy way out and just converted the output of the statement into a csv file; leaving the content unchanged as well as the order.

But, there is some pattern -- I'd recast the parsing to take more advantage of it by first bring the file into memory as a cellstr() array, then clean it up some with some preprocessing...

c=strtrim(textread('For Matlab Question - file 1.txt','%s','delimiter','\n','whitespace',''));
c=c(~cellfun(@(s) all(s==','),c));
c(3:end)=extractAfter(c(3:end),1);

leaves you with

>> c
c =
  18×1 cell array
    {'Exported on: 07/12/2020 10:27 AM ET,,,,,,,,,'                                                                                                              }
    {'Selected account(s):cccc cccccccccc 2 (cccc nnV-nnnnn),,,,,,,,,'                                                                                           }
    {'Account ,Value ,Day's Value Change ,Unrealized Gain/Loss ,  ,,,,'                                                                                          }
    {'ccccc cccccccc 2 cccc nnV-nnnnn ,"$nnn,nnn.nn ",$0.00 0.00% ,"+$nn,nnn.nn +6.90% ", ,,,,'                                                                  }
    {'Symbol Description ,Quantity ,Price ,        Day's Change             ,Value ,        Day's Value Change             ,Unrealized Gain/Loss ,Last Updated ,'}
    {'BSIIX BLACKROCK STRATEGIC INCOME OPPRTNTS PTF INST ,"n,nnn",$9.92 ,$0.00 0.00% ,"$nn,nnn.nn ",$0.00 ,+$637.20 +0.80% ,7/10/2020,'                          }
    {' , , , , ,         Cumulative Investment Return       ,+$637.20 +0.80% , ,'                                                                                }
    {'MBB ISHARES MBS ETF ,nnn,$110.60 ,$0.00 0.00% ,"$nn,nnn.nn ",$0.00 ,"+$2,037.61 +2.97% ",7/10/2020,'                                                       }
    {'VCIT VANGUARD INTRMDIATE-TERM CORPORATE BOND ,712,$nn.nn ,$0.00 0.00% ,"$nn,nnn.nn ",$0.00 ,"+$8,250.38 +13.82% ",7/10/2020,'                              }
    {' , , , , ,         Cumulative Investment Return       ,"+$1,143.78 +1.94% ", ,'                                                                            }
    {' Cash Balance   , , , ,$0.00 , , , ,'                                                                                                                      }
    {'Short Term Gain , , ,"$28,803.10 ", ,,,,'                                                                                                                  }
    {'Short Term Loss , , ,"($7,678.40)", ,,,,'                                                                                                                  }
    {'Total Short Term Gain/Loss , , ,"$21,124.70 ", ,,,,'                                                                                                       }
    {'Long Term Gain , , ,"$40,551.95 ", ,,,,'                                                                                                                   }
    {'Long Term Loss , , ,"($7,143.74)", ,,,,'                                                                                                                   }
    {'Total Long Term Gain/Loss , , ,"$33,408.21 ", ,,,,'                                                                                                        }
    {'Total ,"$nnn,nnn.nn ",$0.00 0.00% ,"$nn,nn.nn ", ,,,,'                                                                                                     }
>> 

to then parse.

I'd then proceed with the rest of the preprocessing to separate out/locate the header line beginng w/ "Symobl Description.." and isolate the secuirties with/without the secondary cumulative return data. One presumes there are more than the three holdings so there are more of these records than shown.

If you build that indexing array that would here be [5 (header) 6 8 9 (three holdings)] and [7 [] 10] for the returns, you could use readtable on the first as a group and then augment the variables with the parsing of the returns data for those holdings for which data are provided.

I would recommend building an importtextfile object to use with readtable as described in the doc for detectImportOptions. This can be done once and then saved for reuse to be redone only if they change the format of the files on you at some point.

Then is the somewhat more painful part -- use regular expressions to strip the $ and embedded commas from the currency fields as TMW has never deigned to build a format field for it (unless it is in some econmics-related toolbox I don't have and have enver investigated). Once that is done, then the only commas left are actually field delimiters and you can safely use readtable. You will, at that point, still have the couple of fields that have multiple values in the field of value and percentage that will have to parse those combination fields to separate the two variables for final presentation (unless you're satisfied to simply have them for viewing rather than actual analysis).

Or, the alternative would be to leave those and substitute ";" for commas everywhere that is not in a currency field; that's what should have been done when the file was built. But, they did at least use quoted strings it looks like so that the '%q' will scan them and you'll get the fields that can more easily be parsed individually.

Hopefully, that will get you started on writing somewhat more generic parser...

dpb 2020-8-2

在 MATLAB Online 中打开

You also asked about comments on style --

Firstly, not sure why you left the input1,input2,input3 arguments in the function statement when you don't use any -- means you have to have something in the calling statement to even run the function for debugging...a function doesn't have to have any arguments if it doesn't need anything from the caller.

I'd handle the file handling a little differently; I wouldn't cd to a location; you did well in setting the data directory path (that would be a candidate for an optional input argument, btw, instead of hardcoding, with the default as given), but instead use the directory in fullfile() to generate fully-qualified filename. Then you can put code and work in a different location than the data storage area more easily--

...
dataPath = "C:\Users\marks\OneDrive\Documents\ML Data";  %Unprocessed .CSV files and processed .mat file
...
d=dir(fullfile(dataPath,'*.csv'));          % return dir() stuct of .csv files
nFiles=numel(d);                            % get number returned; save if need elsewhere
for i = 1:nFiles
  [fid, errmsg] = fopen(fullfile(d(i).folder,d(i).name));
  ...

As shown above, I'd also read the files first into memory and then parse from there rather than using fopen() and fgetl() because then you will be able to use some of the vectorized processing by judiciously selecting subsets of records instead of doing each as it comes independently

It would then likely be good to write the parsing as smaller routines that do pieces and pass back ther part, but I hadn't time to try to get farther than the first very cursory cleanup that I showed...

dpb 2020-8-2

在 MATLAB Online 中打开

BTW.

function [output1,output2] = importMLdata %(input1,input2,input3)

takes care of the spurious inputs...

Mark Smith 2020-8-8

DB, there is not an Accept Answer button since you posted comments and not an Answer to my question about parsing irregular financial data. I would like to give you credit for providing me an answer that worked. The 'detectImportOptions' suggestion was key. Use of the 'detectImportOptions' output causes the data needed to be parsed into a table format in a single line of code. The results can then be manipulated in a table to remove unwanted lines.

I would show you the code but I was working in the command window to test things when the machine crashed. I have just now gotten back to the issue. The crash was due to a bad memory stick which had to be replaced.

I would appreciate the Admins crediting you with answering my question if that is possible.

请先登录，再进行评论。

请先登录，再回答此问题。

Parsing of financial data file that is very irregular in format

7 个评论
显示 5更早的评论隐藏 5更早的评论

回答（0 个）

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

Parsing of financial data file that is very irregular in format

7 个评论 显示 5更早的评论隐藏 5更早的评论

回答（0 个）

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

7 个评论
显示 5更早的评论隐藏 5更早的评论