Extracting financial information from Manage Your Money (MYM) Files

You can figure out where most of the data in your Manage Your Money (MYM) files by creating a small, representative, data set and then browsing the DBF files using Excel, Access, etc.
 
However, below are some notes to help you along....
High Level Entities
  • The high-level entities are stored in the ACCOUNTS table
  • INSTANCE is the key field
  • SUB_TYPE is "11"
Account Data
  • Accounts are stored in the ACCOUNTS table
  • INSTANCE is the key field
  • PINSTANCE can tells you the "parent" instance. You can JOIN the PINSTANCE field back to INSTANCE in the ACCOUNTS table (or a query based on this table) and select the NAME field to get whether the original account is an asset or a liability. These records have SUB_TYPE of 11.
    2 - Assets
    3 - Liabilities
  • The name of the account is in the NAME field
  • You can get the Account Number by a JOIN on the NAME in the ACCOUNTS table to NAME in the CARD table and taking the ACCT_NUM field from the CARD table
  • The type of account is indicated by the SUBTYPE field
    1 - Checking account
    2 - Savings account
    3 - Cash account
    4 - Credit card account
    5 - Other asset account
    6 - Investment account
    7 - unknown, may not be used
    8 - unknown, may not be used
    9 - Loans (Other Liability) account
    10 - used for categories, see below
    11 - used for Net Worth, Equity, Assets, Liabilities, Income and Expense records
  • OPEN_BAL in ACCOUNTS is the Opening Balance
  • CURR_BAL in ACCOUNTS is the Current Balance

Categories (Budget Categories)
  • Budget Categories are stored in the ACCOUNTS table
  • INSTANCE is the key field
  • PINSTANCE can tells you the "parent" instance. You can JOIN the PINSTANCE field back to INSTANCE in the ACCOUNTS table (or a query based on this table) and select the NAME field to get whether the original category is an Income category or Expense category. These records have SUB_TYPE of 11.
    4 - Income
    5 - Expense



Some additional notes:
  • Categories have subtype 10 in Accounts table. Categories have depth 2, and subcategories have depth 3. The "supercategory" of a subcategory is in field Pinstance.
  • A non-zero field tax_token in Accounts table means a tax-related category
  • 5 in the field Pinstance in Accounts table means an expense category. 4 means an income category
  • Account numbers for account description are in field Acct_num in Card table, indexed by account name in field Name
  • Budget amounts for categories are in budget table, indexed by account instance in field acct_inst
  • Tax form for Tax schedule information is in Form field of Tax table, indexed by tax token in Token field
  • Transaction Dates are in trans_dt field of Trnalloc table
  • Transaction Amounts are in amt field of Trnalloc table
  • Transaction Cleared Status are in Reconciled field of Trnalloc table
  • Transaction Check/Reference Numbers are in ID field of Trnalloc table
  • Transaction Payees are in Card_name field of Trnalloc table
  • Transaction Memos are in Comment field of Trnalloc table
  • Transaction Addresses are in Card table, indexed by name

 
Good luck!
 
- Brian

No comments: