Patent Application
20080215619
The present invention relates to management of data and is particularly useful for the management of financial data from disparate sources and in different formats from different accounts, assets and investments.
Managing personal financial information includes organizing a flood of information from various incoming statements and in various formats including: bank statements, quotes, notes of phone calls with a broker, notes of new understandings of the financial portfolios, etc. Management of personal data is conventionally performed with the use of a spreadsheet application program. First, a decision is made regarding how to present the data in spreadsheet tables and then data is entered into the spreadsheet tables from each incoming statement. Alternatively the statements are available in an electronic format and an existing computer application is used which imports the electronic statements assuming the format is supported by the application. Otherwise the data may be input manually into the application. Occasionally, the underlying model, e.g. the structure of the spreadsheet tables, is changed requiring a modification of the spreadsheet table structure. If a computer application is being used, migrating to a newer version of the application or to a different application typically requires a change in the underlying model. A model change is also needed for instance whenever there is a change in assumptions: e.g. change in tax laws, change in personal tax situation or whenever new financial information needs to be represented in the model. The situation is further complicated when working with tax systems simultaneously from several countries. Tax rules change and as a result the data model needs to change. All tools assumes there is just one truth about the data which is called the “consolidated” information. The user is required to perform the endless task of manually verifying the consolidated information at any point in time.
A change of model may also be required whenever a new type of statement is input, even if the new statement contains just one new field not previously considered in the model. Changing the model, e.g. spreadsheet tables currently in use and the relations between them, can be a daunting task: existing data requiring restructuring, and import tools have to be modified.
Commercial financial software packages (e.g. Intuit Quicken or QuickBooks, Microsoft Money or gnucash) are designed to manage checking accounts especially if they are held in a US bank or perform bookkeeping. These packages typically have limited functionality for managing investments. An import tool is required for importing information from each statement each in the appropriate format, e.g. spreadsheet, comma separated values (CSV). Each format requires bank specific processing depending on the financial institution. (CSV is one implementation of a delimited text file, which uses a comma to separate values.) Even if the financial institution exports information in a well defined file format (e.g., Open Financial Exchange, OFX) the commercial packages are not guaranteed to handle the disparate formats correctly if at all.
OFX is a data-stream format for exchanging financial information. The OFX standard was designed as a unified technical specification which allows for bank and application specific extensions. Few banks support OFX as a vendor-independent format, preferring to support a narrow subset used only by a specific financial software application, such as Quicken.
When using an existing financial management application it is hoped that the required model currently in use will be supported in future versions and support for the computer application in use will not be discontinued. In existing software packages, questions about the financial data, are either programmed already, or if not hopefully support will be available in the next version. Other software packages are available to analyze current investment portfolio and give investment suggestions for the future but they are not particularly suitable for collecting information, looking back at history, or just showing in great detail the current investment position. Some banks supply Web tools that show your current portfolio but they lack information from other financial institutions without generating exposure between banks.
There is thus a need for, and it would be highly advantageous to have a method of managing, consolidating and analyzing information, such as personal financial information and investments which comes from disparate sources.
According to the present invention there is provided a computerized method for managing data performed in a computer system. The data is stored in a data base including flat lines. Each of the flat lines includes values of the data and tags associated with the values forming tag-value pairs. The flat lines are scanned and an object of a class is created. The class is specified in a user defined model. The class specifies a subset including one or more tags selected from the tags of the flat lines. A flat line points to the object if a portion of the tag-value pairs of the flat line includes all the tags of the subset. When the flat line contains multiple alternative ways, through the subset(s) of tags or through different tag-value pairs, of pointing to the object of the class, then the object as individually identified by the alternative ways is taken to be the same object.
Typically, multiple statements including the data are input in two or more different formats and the data are converted into flat lines in the data base. The data of the data base are invariant with changing the order of the tag-values pairs within any flat line of the data base. The class and the object are typically added to all the flat lines in which the object is identified. A second class preferably specifies a second subset of tags to be used as an identifier for a second object of the second class. One of the tags of the second subset is the name of the (first) class and the (first) object is the value associated with the one tag of the second subset.
The scan is preferably repeated until no additional objects are created. A query on the data base is optionally performed based on the tag over the flat lines. The query includes at least one triplet consisting of a tag name, a comparison operator and a value. When the query includes multiple triplets, a logical OR is assumed for all the triplets that have the same tag name and comparison operator; and a logical AND is assumed between all other of the triplets. When scanning, if multiple objects are created when only one of the objects is desired by a user, thereby causing a conflict or ambiguity in the data, additional flat lines are preferably inserted into the data base for resolving the conflict or ambiguity. The scanning is typically performed based on a model defined by the user and when the user changes the model to another model, the objects are created according to the second model by scanning again the flat lines of the database. When multiple classes are specified, a single flat line points to at most one object for each class specified.
A portion of the tags are a set of attributes of the object as defined in said class. The object is created by (i) during the scanning, extracting all of the attributes which are found in the flat lines that are pointing to the object, (ii) For each of the attributes found in the line(s), all values that appear in the line(s) are appended to the attribute while verifying that each value assigned appears no more than once; and (iii) the name of the class is added as another tag to the line(s) and assign said object as a value to said other tag. A tag in the set of attributes may behave as a filter by testing if the line(s) match the filter. The values assigned to the attribute in all flat-lines identifying the object are optionally cached in an object database operatively connected to said database. The caching is performed by either modifying a previously cached value or by caching a new value.
According to the present invention there is provided a computerized method for managing financial data performed in a computer system. The data are converted into a data base including multiple flat lines. Each of the flat lines includes values of the data and tags associated with the values, to form multiple tag-value pairs and at least one of the tags is associated with at least two values of the data. The data of the data base are invariant with changing the order of the tag-values pairs within any flat line of the data base. The flat lines are scanned, thereby creating an object of a class. The class specifies a subset including one or more tags selected from the tags. The flat line points to the object if a portion of the tag-value pairs of the flat line includes all the tags of the subset. At least two of the flat lines preferably have at least one different tag. Statements including the financial data are input in at least two different formats from different financial institutions including investment brokers and banks.
According to the present invention there is provided a computer readable medium by a machine, tangibly embodying a program of instructions executable by the machine to perform a method for managing data, the method being performed in a computer system comprising the steps as disclosed herein.
The invention is herein described, by way of example only, with reference to the accompanying drawings, wherein:
The present invention is of a computerized method for managing information. Specifically, the method includes converting all incoming information from multiple sources of financial data into a flat data structure, and using the flat data structure to resolve conflicts and ambiguities in the financial data, perform queries and build objects from previously determined classes of objects defined in a user specified model.
According to embodiments of the present invention disclosed herein is an innovative approach wherein the information of the incoming statements is given and invariant. The input information is converted into an intermediate data structure including “flat lines” which include a representation of the all information from all incoming statements. The structure of the flat lines is preferably simple so that the flat line data structure can be read and edited manually. After the flat line intermediate data structure is created, the flat lines are scanned in order to create objects according to a model. If the model is modified, the scanning process is performed again, and based on the new model, new data objects are created. Hence, the building of the model is separate from the representation of the statements: flat lines remain invariant as the incoming statements they represent are invariant and there is no need for a migration process whenever the model is changed.
According to embodiments of the present invention if there are conflicts or ambiguities between any of incoming statements, the conversion into flat lines of the incoming statements is performed while the resolution of the conflicts and/or ambiguities is deferred to the object creation process. In prior art methods, e.g. spreadsheets, computer applications, a consolidation process needs to be performed while inputting information, in which a single “truth” is selected from the ambiguous or conflicting data and the “single truth” is entered in the spreadsheet tables or input into the computer application.
The use of flat lines is simple to understand. As a result it is easy to:
The object based model is easy to follow and modify it is therefore easy to:
It should be noted, that although the discussion herein relates to management of financial information from multiple sources, e.g. bank statements, the present invention may, by non-limiting example, alternatively be configured as well for management of other types of information. A few examples of different types of information which may be unified by embodiments of the present invention include: scientific research data, environmental data, geological data, meteorological data, pharmacological data, epidemiological data, medical statistics, health care statistics, economic statistics, educational statistics, anthropological data, sociological data, zoological data, military data, from different researchers, organizations, countries and/or continents.
Before explaining embodiments of the invention in detail, it is to be understood that the invention is not limited in its application to the details of design and the arrangement of the components set forth in the following description or illustrated in the drawings. The invention is capable of other embodiments or of being practiced or carried out in various ways. Also, it is to be understood that the phraseology and terminology employed herein is for the purpose of description and should not be regarded as limiting.
The embodiments of the present invention may comprise a general-purpose or special-purpose computer system including various computer hardware components, which are discussed in greater detail below. Embodiments within the scope of the present invention also include computer-readable media for carrying or having computer-executable instructions, computer-readable instructions, or data structures stored thereon. Such computer-readable media may be any available media, which is accessible by a general-purpose or special-purpose computer system. By way of example, and not limitation, such computer-readable media can comprise physical storage media such as RAM, ROM, EPROM, CD-ROM or other optical disk storage, magnetic disk storage or other magnetic storage devices, or any other media which can be used to carry or store desired program code means in the form of computer-executable instructions, computer-readable instructions, or data structures and which may be accessed by a general-purpose or special-purpose computer system.
In this description and in the following claims, a “computer system” is defined as one or more software modules, one or more hardware modules, or combinations thereof, which work together to perform operations on electronic data. For example, the definition of computer system includes the hardware components of a personal computer, as well as software modules, such as the operating system of the personal computer. The physical layout of the modules is not important. A computer system may include one or more computers coupled via a computer network. Likewise, a computer system may include a single physical device (such as a mobile phone or Personal Digital Assistant “PDA”) where internal modules (such as a memory and processor) work together to perform operations on electronic data.
Those skilled in the art will appreciate that the invention may be practiced in network computing environments with many types of computer system configurations, including mobile telephones, PDA's, pagers, hand-held devices, laptop computers, personal computers, multi-processor systems, microprocessor-based or programmable consumer electronics, network PCs, minicomputers, mainframe computers, and the like. The invention may also be practiced in distributed computing environments where local and remote computer systems, which are linked (either by hardwired links, wireless links, or by a combination of hardwired or wireless links) through a communication network, both perform tasks. In a distributed computing environment, program modules may be located in both local and remote memory storage devices.
The principles and operation of a computerized method for managing financial information, according to the present invention, may be better understood with reference to the drawings and the accompanying description.
Reference is now made to
In this description and in the following claims, a “network” is defined as any architecture where two or more computer systems may exchange data. Exchanged data may be in the form of electrical signals that are meaningful to the two or more computer systems. When data is transferred or provided over a network or another communications connection (either hardwired, wireless, or a combination of hardwired or wireless) to a computer system or computer device, the connection is properly viewed as a computer-readable medium. Thus, any such connection is properly termed a computer-readable medium. Combinations of the above should also be included within the scope of computer-readable media. Computer-executable instructions comprise, for example, instructions and data which cause a general-purpose computer system or special-purpose computer system to perform a certain function or group of functions.
Reference is now made to
CSV file statement 101A is downloaded as follows from a bank and saved under the file name: ‘Bnk1-061012.csv’:
The structure of the data of the above CSV file is represented in a structured table as follows:
File Bnk1-061012.csv has a structure to that is unique to the bank and in addition the values have a syntax which may be unique. For example, the Account# relates to the entire file and the dates are in US format. The first step in processing this file is to convert (step 103) the structure of the information contained in Bnk1-061012.csv into “flat lines” and normalize the syntax being used. For example:
(The symbol “\” is used to indicate that the present line continues although presented as folded in order to fit on a page.) At this point the original structure (e.g row, column) of the file or table is removed. Each line is structure less and stands for itself, i.e. the lines are “flat”, without referring to the structure of original file 101. The tabular structure of file 101 in which each column has a name and a value appears in a row under a certain column is replaced with tag-value pairs, the tag name being a standard name (e.g. ACCTID,SECNAME,UNITS) associated with the column name. The association is represented in the above example with a symbol “=”. Tag syntax 111 of values associated with the tags is standardized and in step 105 the syntax of the data from files 101 is checked against standard syntax 111 and if different the values are cast into standard syntax 111. The flat lines are loaded (step 107) into a data-base 109 with lines arriving from other statements 101A, 101B from other sources in multiple formats.
For example a statement 101B made a month later is reduced to the following three flat lines:
Queries may be performed once the information from the two sources are combined into a single data-base 109 of flat-lines.
For example, the following query:
In this query an AND operation is performed on the different tags CUSIP and UNITPRICE. In the following example, we want to list all the SECID values we have in our data base: SECID!PRINT SECID!SORT SECID!UNIQUE
The pair SECID!UNIQUE cause all lines containing the same SECID value to be printed together in one larger line. This is done to avoid repeated prints of the same value (with the SECID!PRINT pair.) The task of SECID!SORT is to sort the printout according to the value assigned to SECID. The combined pairs SECID!UNIQUE SECID!PRINT are so common that they can be shortened into one pair: SECID!GET.
Next we want to get a sorted list of the names of assets for which we had non zero units in the third quarter of 2006:
An AND operation is performed on DTASOF because it appears with two different comparison operators (>= and <).
Each of the assets may be classified into asset classes (not to be confused with object class) by adding (step 115) lines to database 109 as in the example below:
Prior to this manual work we may want to check if we haven't forgotten to classify any asset. The query: SECID!GET ASSETCLASS? prints all the SECID for which we assigned some value to ASSETCLASS. We can get a list of all assets that are either stocks or bonds or both with the following query:
In this query the tag (ASSETCLASS) has multiple values and a match with a line is performed if one of the values assigned to the tag in the query is the same as one of the values assigned to the same tag in the line. In a previous example we have seen that an AND is performed on all different tags in the query and now we see that an OR is performed on all values assigned to the same tag with the same comparison operator.
What will happen if we also download price information in a file 101B from finance.yahoo.com. The web site information is converted (step 103) into flat lines with the assumptions CURRENCY=USD and FILE=yahoo as follows in the example below:
Note that the same asset (e.g. Microsoft shares) has different tag-value identifiers, SECID=TICKER=MSFT and CUSIP=594918104 respectively from input statement 101B and 101A. In order to assert that the different identifiers are equivalent, the following lines are added (step 115) manually to data base 109:
We want to identify each Asset as an object that is identified by one or more keys. A new class of objects called Asset is defined within the object model 119 in the example below. Objects from class Asset are identified using the SECID tag or you can say that the SECID tag is a key to objects from class Asset:
An engine is run that creates (step 20) objects based on the above definition of Asset and identifies different SECID values as referring to the same Asset. After running object model 119 through engine 20, the objects are built and stored in object database 117. The building of objects from lines of database 109 is made with the assumption that a single line can point to at most one object from a given class. If a single line has more than one key to the same class of objects then it is assumed that the different keys point to one object of the same class. The following query is performed to retrieve all dates/prices:
The query works because it is assumed that each line includes all objects the query is pointed to. An object, e.g. of class Asset, may also be represented as a tag-value pair where the tag name is the class of the object, e.g. Asset, and the value surrounded with { . . . } is again a tag-value pair which forms a key to the object of the class. Using Asset={SECID=CUSIP=34537086} would give the same results, since both keys point to the same object.
You may be interested in how many shares of MSFT you are holding in a specific bank account:
The query mechanism performs a logical AND operation on the match of each tag-value in the query. It then looks for the line with the maximal DTASOF and prints the value of UNITS. Another way of looking at the AND in the above query is to say that we are looking for a Holding object that is identified by a key made from a set of two tags: ACCTID and Asset:
In the class definition of Holding several features are introduced:
As another example, we may give a unique index HIDX for each holding:
In order to add the assertion that the two keys above are referring to the same object the manual line:
HIDX=12 ACCTID=12A-32243 SECID=TICKER=MSFT FILE=manual is added (step 115) to database 109 and run through process 20. Alternatively the following manual line could have been used:
In this example we see that an object's key can be made by more than one set of tags. The same object of class Holding is both referred to with two tags: ACCTID=12A-32243 Asset=SECID=TICKER=MSFT or ACCTID=12A-32243 Asset=SECID=594918104 or it can be referred to using a key made with one tag: HIDX=12. Armed with this new information the same query can now look like:
Reference is now made to
In step 23, database 109 is searched for objects that match the lines, for example, the manual line: HIDX=12 ACCTID=12A-32243 SECID=TICKER=MSFT FILE=manual. If an object is found (decision box 205) then it will be used. Otherwise, if no object is found in decision box 205, then a new object is created (step 219). If more than one object is found in decision box 207, then the objects are merged (step 223). In any case, the attributes of the object are updated in step 209 using tag-value pairs from database 109 and all possible keys are extracted (step 211) from the attributes of the object. This is done by trying all key sets as defined in the class. For each key set, check that all tags appear as attributes of the object and assign a value taken from the object's attribute to form a key. If a tag has more than one value, as an attribute of the object, then generate all combinations as different keys.
Returning to the previous example, in step 211, it is recognized that the manual line contains two different keys to a Holding object: one is from the HIDX tag and the other from the ACCTID tag and from the implicit Asset tag (which was generated earlier using the SECID tag), all possible keys are extracted (step 211). Finally the two different keys are identified as pointing to the same object since both keys were taken from the same manual line. If no key is found in the object (decision box 221), then the object is discarded. Otherwise, if a key is found in the object, in step 215, the object is put in object data base 117 using the keys. For each of the object's attributes as defined in the class, all values that appear in the line are appended (step 215) to the values assigned to the attribute, making sure that each value appears only once. At this point, the line is made to refer back to the object. The class name is added as a tag to the line and assigned to the object as its value. In our example, the pair Asset={SECID=TICKER=MSFT} is implicitly added (step 113) to the manual line in database 109 since the manual line contains enough information to point to that Asset object.
According to an embodiment of the present invention, in process 20 in order to have a tag used in a key being a name of another class of objects (e.g. Asset is a tag used inside a key to Holding), the other class should have been already been processed by algorithm 20. This can be arranged, for example, by iterating over the classes in a specific order (for example, first over Asset then over Holding) and verifying that the relation between classes does not have loops.
Furthermore, when merging (step 223) objects into one object, all lines and all objects (key tag or attribute) referring to each of the old objects should be updated to refer to the new merged object.
In the following example we want to find the index of all holdings of MICROSOFT CP (there may be more than one holding if the same Asset is held in different accounts):
The following query will get us all SECID for which we forgot to enter an ASSETCLASS:
The query ASSETCLASS? finds all lines that do have ASSETCLASS defined. The value of the Asset tag is taken from those lines and next we perform a query Asset!= . . . which looks for lines that have an Asset tag but that does not
match any of these values.
We have seen how lines can point to objects using keys that are the value assigned to set of tags. Several lines are thus grouped as belonging to one object. In essence the group of lines is the object's content. Each tag value pair appearing in any of the lines of the group is an attribute of the object. The values assigned to each tag in all the lines in the group are the values of the attribute of the object. The values taken from different lines form a new binding together of tag-value pairs at the object level. An object can be represented as a new line in database 109 by merging all the tag-value pairs from all the lines in the group. The binding together of information in an object does not affect the lines themselves. On the contrary, a line may point to several objects (as long as they are from different classes.) The original line content is never modified and therefore the fact that the same line may be part of several line groups, each belonging to a different object from a different class, should not interfere.
In the previous examples we have seen how to extract attributes directly from the lines using an appropriate query. For example, an Asset security name (SECNAME) and currency can be extracted with the filter: SECNAME!GET CURRENCY!GET Asset= . . . A drawback of this approach is that a query is performed whenever you want to retrieve an attribute. Another drawback is that you are required to keep track of all relevant attribute tags of each class of objects. According to an embodiment of the present invention, for each class, all attributes (tags) that are of interest are specified and then stored (or cached) in the object in database 117 as a copy of values taken from the group of lines in which the object appeared. For example:
In this example we have listed in the lists stags and mtags all the attributes of interest for an object from the class Asset. The attributes in mtags can have any number of different values or none at all. The attributes in stags are expected to have at most one unique value. The lines in data base 109 may of course accidentally have multiple values even for an stags attribute. For example, COUNTRY=USA and COUNTRY=“UNITED STATES”, as follows:
In the spirit of this invention, conflicts of this sort preferably concern a content verification tool, 123, running over an existing data-base and are preferably ignored when importing the lines to data-base 109. So putting an attribute in either stags or mtags is just an indicator for such a tool and not part of the basic infrastructure. An advantage of having a private copy of the attributes values in each object is that a tool 121 can edit them, without affecting at the same time the original lines of database 109. For example, a subsequent tool can decide that COUNTRY=USA is the preferred value and erase COUNTRY=“UNITED STATES” from the object. The entire attribute content of an object can be printed:
In conclusion, adjusting the model is performed by changing the class definition or by creating new classes by adding/updating lines in database 109 with class-object pairs. The same “flat line” infrastructure is used both for inputting data, generating objects and performing queries.
Consider the following class definition of a single historic price of an asset:
According to an embodiment of the present invention the last tag in the key set, UNITPRICE?, behaves as a filter when testing if the line contains the other tags in the key set. The filter's task is to ensure that a PriceHistory object will appear only in lines that have a UNITPRICE tag regardless of the value assigned to it. The value assigned to this tag is not needed to create a key but the filtering is needed because the key set [‘Asset’,‘DTASOF’] may appear in lines containing other historical facts about the asset.
Next consider the following class:
This will create a new TaxYear object using the first key set: TAXYEAR=2005 and with attributes DTEND=20060101 DTSTART=20050101. Next consider testing if this object appears in the line: DTTRADE=20050202
Here the second key set is expanded to be
Reference is now made to
The query that allowed us to retrieve the entire content of an object (e.g., Asset!GET SECID=TICKER=F) can be used in a programmatic interface to retrieve an Asset object. This object will have all the above attributes set in it and these attributes can be naturally accessed as with any regular object. This simplifies the task of writing tools, object fixing tools 121 and reporting tools 123 (in
Some example of possible tools 121, 123:
The framework allows processing of information coming from different sources by converting them to flat lines. The use of flat lines keeps as much information as possible in the original form in which it was received from the financial institutions. Since queries are performed on the flat lines themselves it is possible to retrieve the original lines and if necessary it is easy to go back to the original line in the original incoming statement that came from the financial institution. The framework resolves conflicts and ambiguities after the data is input by developing tools that resolve the conflicts and ambiguities. In comparison, in a prior art system which converts all incoming data into fixed tables based on a given underlying model, the direct relation to the original document may be lost.
While the invention has been described with respect to a limited number of embodiments, it will be appreciated that many variations, modifications and other applications of the invention may be made.
This application claims the benefit from U.S. provisional application 60/904,399 filed Mar. 2, 2007 by the present inventor. U.S. provisional application 60/904,399 is included herein by reference for all purposes as if entirely set forth herein.
| Number | Date | Country | |
|---|---|---|---|
| 60904399 | Mar 2007 | US |
