If you need to create the HMDA LAR in an Excel spreadsheet file, the process will need to be done outside of the Compliance Data Platform (CDP). Below are the steps from start-to-finish for generating your HMDA LAR and bringing it into Excel.
Generate the HMDA Submission File
-
Log into CDP.
-The active Institution and File will display in the upper right-hand section to the left of the name of the user logged in.
- Go to Filing>HMDA Submission File.
2. Verify the data under the HMDA LAR Submission File heading is correct.
3. Under the Submission File Options heading:
a. Verify the box next to Public LAR (Submission File) is unchecked.
b. Verify the Filing Quarter is set as 4th Quarter and the radio button is selected for Year to Date - If you only want a HMDA LAR for part of the year select the appropriate option for the last quarter of the desired time range.
3. Select Create LAR File - This will generate as a .txt file.
Bring the HMDA LAR file into Excel
4. Open Excel.
5. Select the option for a new workbook.
6. Select File>Open.
7. In the File Browser window, go to the location where the HMDA LAR file is saved.
8. You will likely need to change the file type option in the window from All Excel Files to All Files to see your HMDA LAR .txt file.
9. Select the submission file, then Open - a Text Import Wizard Window will appear.
10. In the first window, keep the Original data type as Delimited and select Next.
11. Under Delimiters, uncheck the box next to Tab, and check the box next to Other, enter the | symbol in the adjacent field to the left of Other, then select Next.
12. Under Data Preview, select the second and third columns, under Column Data Format, select the radio button next to Text, then select Finish.
13. Select the first row in the spreadsheet containing the HMDA LAR data, then right-click and select Clear Contents. This will clear the institution data from the file*.
14. If headers for the data points are needed - Open the CFPB HMDA LAR Structure file (attached at the end of the guide). This will open to a separate spreadsheet consisting of just headers.
15. Select the first row in this file, right-click, then select Copy.
16. Select the first row in the HMDA LAR Excel spreadsheet, then right-click and select Paste to insert the headers
17. Select File>Save As to save the data as an Excel spreadsheet.
Notes
*If you need to keep the institution row, you can right-click on the first row of microdata and select Insert, which will insert an empty row, then copy and paste the headers here instead.