Data Reconciliation Steps
October 2018

Data Reconciliation Steps

One of the most important things each institution needs to do prior to running Visible Equity reports is to make sure the data that is sent over is fully reconciled. Garbage in, garbage out, right? The first step is making sure the file is correct prior to uploading it. Once the data is inserted into Visible Equity, you will then begin to reconcile your active, delinquent, and charge-off balances. It is extremely important to make sure there is at least one person at your institution accountable for the reconciliation process. Along with reconciling those three balances, there are additional reports that will check for data holes and discrepancies within your file. From uploading a file in the correct format to making sure we have all of the right data points, this guide will allow you to analyze your data in the most effective way.

  1. Uploading a file
    1. Your file should be in a .csv or .txt format with the headers in the first row and the data in the columns directly beneath.
    2. The standard naming convention should include the data date of the file, the name of your institution, and a brief description of what data can be found within the file (i.e. 20171031_AwesomeCU_Loans).
    3. Some common errors made by Visible Equity users that will negatively impact or halt the implementation process are:
      • Loan IDs converting to scientific notation prior to uploading.
      • A title, description, or anything else above your column headers.
      • Format of loan IDs changing month to month.
      • A loan ID found on multiple rows within the same file or across different files with inconsistent balance information.
    4. When checking on the status of your file or checking to see if your file uploaded successfully, go to Data > Drop Down Menu by “Upload a File” > File Management. This page will be accessible to portfolio admins, and you can view all files uploaded by any individual at your institution.
    5. On this page, there are several different columns that will tell you the status of your file:
      • DATA DATE: When manually uploading a file, you will need to assign a data date prior to uploading the file. The data date reflects whatever month end snapshot of data you are uploading.
      • BUILD TIME: If this is populated with a date soon after your upload, your file was in the correct format and accepted by the system. If not, check for a red triangle error message to get more insight or submit a ticket. We love tickets!
      • HEADER ID: This will go hand in hand with the “Build Time” column. As soon as the file successfully builds in VE, a header ID will appear. The header ID is assigned to the file based on your column headers. Files with the same headers will share a header ID.
      • IS MAPPED: If your header ID is the same as a previous upload’s, this should populate with a small green checkmark shortly after the build time. If this is a brand new header ID, either your upload is the first of its kind, or you added, removed, rearranged, or changed the spelling of your column headers. Your data analyst at VE will need to manually map the file. Please submit a ticket with any changes you make to a file upload.
      • IMPLEMENT TIME: If all other fields are populated and no errors have occurred, this will populate with a time stamp when the file has finished implementing. At the end of implementation, the calculation process will begin. Please allow up to 24 hours for your file to fully process (given that there are no errors).
    6. Loan Type Mappings Page & Institution Total Assets/Net Worth
      1. Once your monthly files have been uploaded and processed, the next step is to fill out the loan type mappings page. This is where you will assign the type codes sent in your file a description, category, sub-category, lien position, collateral information, etc. Once this is updated, the data should reflect accordingly in VE within a couple of hours. It is not an instantaneous change.
        1. To get there, click on the Settings button at the top of your page.
      2. This only needs to be done once for each type code, so you won’t need to re-fill this out every month. Just be sure to check this page each month for unclassified type codes or newly added ones.
      3. We want the categories and sub-categories to match as closely to your reconciliation source (e.g. GL, concentration policy limits) as possible. If they do not match up, fill this page out anyway and submit a ticket so we can create custom categories and sub-categories to better fit your institution’s needs.
      4. If this is not filled out, you will be unable to use any filters in VE, you will see a lot of “Not Reported” in your reports, and collateral valuations and LTVs will be negatively impacted.
      5. Please do not attempt to reconcile your data based on the loan counts and unpaid balances seen on this page. These numbers are including all loans, including closed and charged-off loans.
      6. On a quarterly basis, we get your institution’s total assets and net worth from the Call Report. However, an admin at your intuition can keep this accurate and up-to-date by filling this out each month in VE.
        • To get there: Settings > Drop down menu next to “Loan Type Mappings” > Institution Parameters > Add New Institution Parameters.
        • You can add this to old data dates as well.
      7. Data Reconciliation: Active Total Portfolio Loans
        1. Please ensure that someone at your institution is reconciling your monthly files prior to uploading.
        2. After the data has been inserted in to Visible Equity, you will reconcile your total active portfolio balance in a Concentrations/Grading report.
          • To get there: Loan Analytics > Position Reports > Concentrations/Grading.
          • The default view will be looking at the standard VE categories. The total balance will reflect all active, open, not-sold If you need to include loans we have flagged as sold (100% sold, not participation loans), go to Loan Data Filter > Advanced Filter > Loan Characteristics > Sold: Include Sold Loans > Apply > Return to Report.
          • You can also change the show option from “Balance” to “Loan Count.”
          • We need to have an indicator in your files to make sure we are flagging closed and charged-off loans correctly so their balances do not appear in this report.
          • If the total balance reflected on this report matches your GL, you’re good to go! Take a nap break. You deserve it. If it does not match, you will want to view this report more granularly. Change your primary stratification from “Category” to “Type Code,” “Type Description,” or whatever custom field we have set up for you. Submit a ticket with which line items are incorrect and what the correct balances and counts should be.
        3. Data Reconciliation: Delinquency Balance
          1. The next step is to balance your delinquent balances in a Delinquency Ratio report.
            • To get there: Loan Analytics > Performance Reports > Delinquency Ratios.
            • Delinquency is defined as greater than or equal to 60 days late. This can be changed if that does not match your institution’s criteria.
            • Most of the time, you are sending a field in your file with an indicator of the number of days late that we are mapping directly as “Days Delinquent.” If you do not have this information in your upload, we can calculate the days delinquent by comparing the data date of the file to the payment due date. Once this hits 60 days, the unpaid balance of each open, active loan will be added here.
            • Like your reconciliation process for regular unpaid balance, if the total delinquent balance is correct, you are good to go! Take another nap. If not, change the primary stratification to something more granular, and let us know in a ticket which is incorrect.
          2. Data Reconciliation: Charge-Off Balance
            1. The next step is reconciling your charge-off counts and balances in a Charge-Off Ratio report.
              • To get there: Loan Analytics > Performance Reports > Charge-Off Ratio.
              • We want this balance to reflect the net charge-off at your institution. Most clients are sending their gross charge-off amounts and dates, as well as any and all recovery payments and dates. If sending individual recovery transactions is not possible, we can map the unpaid balance as the net charge-off. The month-to-month balance change will reflect the recovery payments made.
              • The standard look back period in this report is one year. If your charge-off balance is accurate, great. If not, change the “Look Back Period:” at the top of the report from “One Year Back” to “One Month Back.”
              • Change your primary stratification to something more granular and figure out which line item is incorrect. Let us know your findings in a ticket and we will help close the gaps.
            2. LTV Summary
              1. The next step is to ensure we have some key information for secured loans.
                • To get there: Loan Analytics > Other Reports > Original LTV Summary.
              2. Some data points to check for in this report:
                • Secured Loans Missing Original Valuation: These are the loans you have flagged as secured in the loan type mappings page that are missing an original collateral valuation. Without an original collateral value, we cannot calculate an original LTV or estimate current collateral valuations and LTVs.
                • Loans Missing Lien Position: Lien position can be classified in the loan type mappings page based on loan type code or on an individual loan basis in your file. The LTV calculations are different for first and second position loans, so we will not be able to calculate an LTV until we know the lien position.
                • Second Position Loans Missing Sr. Lien: For all loans classified as second position, we want the current senior lien balance and/or the senior lien balance at the time the second position loan originated. If you provide us with the original senior lien balance, we will estimate the current senior lien balance using a standard amortization schedule, assuming the 1st is a 30-year mortgage with the national average interest rate at the time the loan was originated. If you (or your Credit Bureau) provide us with the current senior lien balance, and you are unable to provide the original, we will reverse this calculation to find the original balance.
              3. Loan Data Completeness Report
                1. The last step is to finish checking your loan data completeness.
                  • To get there: Loan Analytics > Other Reports > Loan Data Completeness.
                2. Collateral Info:
                  1. You will want to check for collateral completeness for auto loans and real estate loans separately. In this scenario, we will check for auto loans first.
                    • Filter down to just your auto portfolio. Loan Data Filter > Predefined Categories > Consumer Lending > Auto > All Auto.
                    • At the top of the page, click on the “Collateral Info” tab.
                    • Since we are only viewing our auto loans, the collateral address, city, state, and zip code are not applicable and can be ignored.
                    • Check under the “Missing Data Field” for Auto Make, Auto Model, and Auto Year (and if possible, Auto VIN). As always, anything in “blue” is a hyper-link, so you can drill down to the loans that are flagged as missing these data fields.
                    • If we have the original collateral value, make, model, and year, we will update the collateral values on a monthly basis for these loans using Visible Equity’s auto valuation models. For more information on the derivation and implementation of this index, click here.
                  2. Repeat these steps for real estate loans.
                    • Filter: Loan Data Filter > Predefined Categories > Residential Real Estate > All.
                    • “Collateral Info” tab.
                    • Check under the “Missing Data Field” for Collateral Address, Collateral City, Collateral State, and Collateral Zip.
                    • With the original value of the property and these four key points, we will update the valuations each month using either our FHFA and Case-Shiller Hybrid Method or our Zillow Index Method. For more information about these methods, click here. Feel free to submit a ticket if you would like to find out which index your portfolio is currently using or if you would like to change your current index.
                  3. Loan Info:
                    • The “Loan Info” tab will review pertinent loan-centric data points. Like the collateral info tab, keep in mind that some fields are not applicable to certain loans (e.g. auto loans missing credit limits). Feel free to use a filter when viewing this report and look at only related fields.
                    • When filling in the holes, we prefer that the missing data is added directly to your regular monthly loan upload. If this is not possible, you can fill most holes with a patch file that contains the loan id number and the missing data point(s).
                  4. Borrower Info:
                    • Like the “Loan Info” tab, this page will provide insight on how many loans are missing standard borrower information.
                    • Since these are borrower-centric data fields, they are connected primarily through SSN or a customer ID number rather than a Loan ID.
Related Blog Posts
Further Your Education