Skip to content
BOL Conferences
Thread Options
#2128008 - 04/26/17 02:24 PM HMDA File Format Verification Tool
JWills, CRCM Offline
Diamond Poster
JWills, CRCM
Joined: May 2013
Posts: 1,781
The Mitten State
This isn't for 2018, but for the new formatting for submission of 2017 data. I have input my information into the CFPB's HMDA Formatting Tool, created my HMDA LAR, and then when I went to the format verification, I have 153 errors for 16 entries that all state my entry in not an integer. I have gone into the Loan Type field, formatted those cells to 'Number' created my new HMDA LAR and when I went to the format verification, the results were the same. I am not sure what I am doing wrong. I have emailed hmdahelp, but I don't know how long that will take for a response. Any suggestions?
_________________________
Nonsense wakes up the brain cells.

--Dr. Seuss

Return to Top
HMDA

   
HMDA Academy
#2128014 - 04/26/17 02:50 PM Re: HMDA File Format Verification Tool JWills, CRCM
raitchjay Offline
Power Poster
Joined: Oct 2009
Posts: 9,088
OK
This is just a guess, but for rate spreads or geocoding entries that need to start with a "0", you have to enter a single quotation mark (') and then the number or it won't save properly.
_________________________
I'm fixin' to fix that.

Return to Top
#2128015 - 04/26/17 02:57 PM Re: HMDA File Format Verification Tool JWills, CRCM
JWills, CRCM Offline
Diamond Poster
JWills, CRCM
Joined: May 2013
Posts: 1,781
The Mitten State
Oddly enough the geocoding entries are fine. I have not had a rate spread to enter. I may try that just to see.

These are the items that have the error "Not an integer"
Loan Type
Property Type
Loan Purpose
Owner Occupancy
Loan Amount
Preapprovals
Type of Action Taken
Date of Action
Applicant Ethnicity
Co-Applicant Ethnicity
Applicant Race
Co-Applicant Race
Applicant Sex
Co-Applicant Sex
Type of Purchaser
HOEPA Status
Loan Status
_________________________
Nonsense wakes up the brain cells.

--Dr. Seuss

Return to Top
#2128017 - 04/26/17 03:04 PM Re: HMDA File Format Verification Tool JWills, CRCM
raitchjay Offline
Power Poster
Joined: Oct 2009
Posts: 9,088
OK
That only applies if you have to enter a "0" first.....it shouldn't apply to typing in a "1" for loan type, etc. I don't know.
_________________________
I'm fixin' to fix that.

Return to Top
#2128022 - 04/26/17 03:30 PM Re: HMDA File Format Verification Tool JWills, CRCM
raitchjay Offline
Power Poster
Joined: Oct 2009
Posts: 9,088
OK
JW....can you tell me what you mean by "when i went to the format verification"? Where's that? I'm curious about my own LAR now and if i'm gonna see something similar, but i don't know where that is? Do you have some other software that is giving you the error messages?
_________________________
I'm fixin' to fix that.

Return to Top
#2128027 - 04/26/17 03:40 PM Re: HMDA File Format Verification Tool JWills, CRCM
MScarn6942 Offline
Platinum Poster
Joined: Aug 2015
Posts: 756
Land Lacking in Lakes, IL
raitchjay (or anyone else that may run into this), you can do a custom format in Excel that will force a 0 in front of a figure if there aren't X number of characters. It looks like "0000.00" in the type field under custom in format cells. Just put however may 0's you want. So if I enter 221 in census tract, Excel will spit out 0221.00.

Hope this can help someone smile
_________________________
"Pressure is something you feel when you don't know what you're doing" - Peyton Manning

Return to Top
#2128028 - 04/26/17 03:42 PM Re: HMDA File Format Verification Tool JWills, CRCM
raitchjay Offline
Power Poster
Joined: Oct 2009
Posts: 9,088
OK
You vastly overrate my Excel skills....LOL....but thanks for that info. I just (try to) remember to put that silly quotation mark first. Thanks.
_________________________
I'm fixin' to fix that.

Return to Top
#2128036 - 04/26/17 03:55 PM Re: HMDA File Format Verification Tool JWills, CRCM
JWills, CRCM Offline
Diamond Poster
JWills, CRCM
Joined: May 2013
Posts: 1,781
The Mitten State
I am trying to attach the link I have saved.

https://www.consumerfinance.gov/data-research/hmda/tech-preview
File Format Verification Tool

In early 2017, the CFPB will also make available an electronic file format verification tool for filers who wish to confirm that a LAR is formatted in the required pipe delimited text file format. This verification tool will be available on a webpage that will run independently from the HMDA Platform, without the need for any special software (*minimum browser requirements will be provided at a later date). The tool will conduct the same initial checks that the future HMDA Platform will perform, and will provide a convenient test mechanism for filers, prior to the roll out of the full HMDA Platform



This website is a work in progress

This is part of our work to improve the HMDA electronic reporting process for financial institutions.

For more information about filing your HMDA data, please visit https://www.ffiec.gov/hmda/.


File Format Verification Tool

Select a HMDA file from your computer and test whether it meets certain formatting requirements needed to submit HMDA data collected in 2017 to the HMDA Platform. The File Format Verification Tool does not test for compliance with edits. No data, HMDA or otherwise, is transmitted from your computer to the CFPB.










The File Format Verification Tool (FFVT) is a resource for testing whether your file meets certain formatting requirements specified in the HMDA Filing Instructions Guide, specifically that the file (1) is pipe-delimited; (2) has the proper number of data fields; and (3) has data fields formatted as integers, where necessary. The FFVT does not allow you to submit HMDA data.

The FFVT uses “client-side testing” which runs on the user’s computer and does not transmit any HMDA data over the internet. Thus, no Federal agency will receive or be able to view the files you test using it. The FFVT was developed with no server logging technology, no login functions, and does not log identifying information about you or your files. The FFVT simply allows HMDA filers to test the formatting of their files.

The FFVT will run efficiently for most files, but it will run more slowly for larger files (e.g., containing more than 20,000 entries).

This website essentially runs on the same software as the HMDA Platform. This means that if your file passes all the checks on this website, then your file will be in the correct format required to be uploaded to the HMDA Platform. The FFVT does not check for compliance with HMDA edits.
Last edited by JWills; 04/26/17 03:58 PM.
_________________________
Nonsense wakes up the brain cells.

--Dr. Seuss

Return to Top
#2128037 - 04/26/17 03:57 PM Re: HMDA File Format Verification Tool JWills, CRCM
JWills, CRCM Offline
Diamond Poster
JWills, CRCM
Joined: May 2013
Posts: 1,781
The Mitten State
How do I know my HMDA file is formatted correctly? Is there a tool that can help?

Yes, the CFPB provides the File Format Verification Tool. HMDA filers can use the tool to test whether their file meets certain formatting requirements provided in the Filing Instructions Guide for HMDA data collected in 2017. The web-based tool is for filers’ convenience and is not part of the HMDA filing process. It does not transfer any data to the CFPB.
_________________________
Nonsense wakes up the brain cells.

--Dr. Seuss

Return to Top
#2128045 - 04/26/17 04:26 PM Re: HMDA File Format Verification Tool JWills, CRCM
raitchjay Offline
Power Poster
Joined: Oct 2009
Posts: 9,088
OK
I'm confused now.....i thought we were still waiting on the CFPB to come out with that.
_________________________
I'm fixin' to fix that.

Return to Top
#2128046 - 04/26/17 04:29 PM Re: HMDA File Format Verification Tool JWills, CRCM
JWills, CRCM Offline
Diamond Poster
JWills, CRCM
Joined: May 2013
Posts: 1,781
The Mitten State
This is just a verification tool that they have created. They do not have the 'program' that we will use yet. Apparently it is still a work in process. I just tried again, downloaded the form again, and input the information for the Transmittal Line, and then information for one loan entry. No errors. So I thought maybe the first one I had done was somehow messed up. I input information for loan number 2, and received 17 formatting errors. All the same--'not an integer'.
_________________________
Nonsense wakes up the brain cells.

--Dr. Seuss

Return to Top
#2128048 - 04/26/17 04:32 PM Re: HMDA File Format Verification Tool JWills, CRCM
JWills, CRCM Offline
Diamond Poster
JWills, CRCM
Joined: May 2013
Posts: 1,781
The Mitten State
I just went back in and removed my respondent id and agency code for lines that I did not have loan information in, and tried again--no errors. I am thinking that may have been my problem. On to the final 14 entries to see how it goes. I'll keep you posted.
_________________________
Nonsense wakes up the brain cells.

--Dr. Seuss

Return to Top
#2128087 - 04/26/17 06:13 PM Re: HMDA File Format Verification Tool JWills, CRCM
JWills, CRCM Offline
Diamond Poster
JWills, CRCM
Joined: May 2013
Posts: 1,781
The Mitten State
It was my own mistake. I had the two items already filled in for lines that did not have a loan entry. All is good in the land of HMDA. If you believe that, I have swamp land for sale. smile
_________________________
Nonsense wakes up the brain cells.

--Dr. Seuss

Return to Top
#2128288 - 04/27/17 07:01 PM Re: HMDA File Format Verification Tool JWills, CRCM
Catm1991 Offline
100 Club
Joined: Jul 2014
Posts: 194
MO
So we DO NOT have to purchase a special software, like quest soft or Lazer Pro? The CFPB will have one?

Return to Top
#2128289 - 04/27/17 07:04 PM Re: HMDA File Format Verification Tool JWills, CRCM
Catm1991 Offline
100 Club
Joined: Jul 2014
Posts: 194
MO
I know we have to make the purchase to get our LEI #, but I was thinking we had to purchase a software to submit our HMDA data from the formatting tool they have provided....

Which if we do not, then boy our President will be happy, because our FI does not want to purchase a software, they have been wanting me too find a different way to take on the new 2018 HMDA overhaul, they DO NOT want to spend the $$$$. But I know we have to pay the $219, for the LEI number then an annual fee for that.

But I want to be sure I'm understanding correctly....we do not have to purchase a software?????

Return to Top
#2128302 - 04/27/17 07:46 PM Re: HMDA File Format Verification Tool JWills, CRCM
Dan Persfull Offline
10K Club
Dan Persfull
Joined: Aug 2002
Posts: 47,517
Bloomington, IN
The testing JWillis is doing is for submission of the 2017 data that is being input to the CFPB HMDA Data Formatting Tool.

Unless you have a very small volume to report I think it will be difficult to report the 2018 data due to be submitted in 2019 without automation. Having to calculate the LEI is in itself going to me a nightmare without software support.
_________________________
The opinions expressed are mine and they are not to be taken as legal advice.

Return to Top
#2128310 - 04/27/17 08:27 PM Re: HMDA File Format Verification Tool JWills, CRCM
Catm1991 Offline
100 Club
Joined: Jul 2014
Posts: 194
MO
Ok, that's what I thought, and I'm trying to convince our President, but he is just not wanting to spend the $$.

But will the HMDA Platform that will be coming out be used for submission if we go that route? I am the only one that does HMDA, and I average around 700 on my LAR, but with the changes it's going to almost triple, so I'm thinking it's going to increase to around 1500-2000.

Thanks.

Return to Top
#2128318 - 04/27/17 08:44 PM Re: HMDA File Format Verification Tool JWills, CRCM
Dan Persfull Offline
10K Club
Dan Persfull
Joined: Aug 2002
Posts: 47,517
Bloomington, IN
The CFPB is in the process of designing a submission tool for 2017 but I'm not sure about 2018.

If your reporting numbers are going to approach what you are anticipating then quite frankly your President needs to wake up and turn lose of the dollars. The potential for mistakes and reporting errors without the software will end up costing you more in resubmission time and CMPs than the software.
_________________________
The opinions expressed are mine and they are not to be taken as legal advice.

Return to Top
#2128319 - 04/27/17 08:47 PM Re: HMDA File Format Verification Tool JWills, CRCM
Catm1991 Offline
100 Club
Joined: Jul 2014
Posts: 194
MO
I agree....

Return to Top
#2128320 - 04/27/17 08:48 PM Re: HMDA File Format Verification Tool JWills, CRCM
Catm1991 Offline
100 Club
Joined: Jul 2014
Posts: 194
MO
I had a meeting with Quest Soft, and I was impressed with what all it could do and also with CRA. I've heard a lot of good feedback with this software as well. I guess I'll see what happens.

Return to Top
#2128335 - 04/28/17 12:15 AM Re: HMDA File Format Verification Tool JWills, CRCM
SMQ, CRCM Offline
Power Poster
SMQ, CRCM
Joined: Apr 2001
Posts: 4,828
Between the lines
I am not directly impacted by this anymore, but did I hear that loan origination software would be able to generate the LEI?
_________________________
NOLA is my Beach!

Return to Top