Skip to content
BOL Conferences
Thread Options
#728347 - 05/08/07 06:03 PM Pivot tables and HMDA LARs
RR Becca Offline
Power Poster
RR Becca
Joined: Sep 2004
Posts: 5,249
out of the frying pan...
(As promised Truffle!)

I see comments on here all the time about people using Excel pivot tables to proof their LARs. This concept fascinates me, but I'm not quite sure how to apply it to actually make my existing system any easier.

Could those of you who do use pivot tables explain (briefly) how it works for you?

Thanks!
_________________________
You call it ADD. I call it multi-tasking.

Return to Top
HMDA

   
HMDA Academy
#728382 - 05/08/07 06:42 PM Re: Pivot tables and HMDA LARs RR Becca
Truffle Royale Offline

10K Club
Joined: Jul 2003
Posts: 17,395

We all know that LARs are basically spreadsheets with columns of information. Consisely stated: Excel pivot tables allow you to "pivot" one column of information against another.

Say you wanted to make sure all your denied loans had denial codes entered. On a LAR, these two columns have multiple columns inbetween them making comparison difficult.

In a pivot table, you can 'lift' the action column and put it in a table reading 1-5 on the left hand side which will read as rows across. Then you grab the denials column and put it across the top of the table so you have 9 columns of denial reasons.

If you have 27 denied loans that have a denial code of 4, you'll get the number "27" in row three, column number 4.

Conversely, if you get a number in row 1, you know you've got an originated loan that has a denial code in it erroneously. By double clicking on that number, you'll get the loan number and all the HMDA information for the loan in question. This makes verifying and correcting the error a snap.

You can also create charts from the pivot table information. I have some PDF examples of this. PM me if you'd like me to send them to you.

Using pivot tables has eliminated virtually ALL of my HMDA errors. We aren't a CRA reporter so you'll have to figure out how all this may work for you in that area.

Return to Top
#729661 - 05/10/07 02:14 PM Re: Pivot tables and HMDA LARs Truffle Royale
CSpellman Offline
100 Club
Joined: Nov 2000
Posts: 176
Andy Z wrote 2 excellent articles in the ABA Compliance Magazine about pivot tables and HMDA Data. They are in the July/August 2006 and September/October 2006 issues. Since they are copyrighted, if you don't have them, you should contact the ABA for the articles.
_________________________
...but I saved a lot on my auto insurance

Return to Top
#730531 - 05/11/07 04:10 PM Re: Pivot tables and HMDA LARs CSpellman
Truffle Royale Offline

10K Club
Joined: Jul 2003
Posts: 17,395
I'm glad to hear you found the articles excellent, CSpellman. I was wondering how they went over with fellow HMDAites.

Return to Top
#1537228 - 04/19/11 01:39 PM Re: Pivot tables and HMDA LARs Truffle Royale
Sage Offline
Platinum Poster
Joined: Aug 2005
Posts: 914
Can pivot tables help identify eventual Q24 issues? That is where the FFIEC questions the amount of income reported when it is under $9000.

Return to Top
#1537230 - 04/19/11 01:42 PM Re: Pivot tables and HMDA LARs Sage
Kathleen O. Blanchard Offline

10K Club
Kathleen O. Blanchard
Joined: Dec 2000
Posts: 21,293
It could, but you could also just do a sort or filter by income and look at all less than $9000.
_________________________
Kathleen O. Blanchard, CRCM "Kaybee"
HMDA/CRA Training/Consulting/Mapping
The HMDA Academy
www.kaybeescomplianceinsights.com

Return to Top
#1537245 - 04/19/11 01:58 PM Re: Pivot tables and HMDA LARs Kathleen O. Blanchard
Sage Offline
Platinum Poster
Joined: Aug 2005
Posts: 914
Would you look at all under $9000 or just originations?

Return to Top
#1537246 - 04/19/11 01:59 PM Re: Pivot tables and HMDA LARs Sage
#12 Offline
Diamond Poster
Joined: Jun 2005
Posts: 1,343
I look at all of them, just to make sure they are correct.
Last edited by #12; 04/19/11 01:59 PM.
_________________________
CRCM

Return to Top
#1537248 - 04/19/11 02:00 PM Re: Pivot tables and HMDA LARs Sage
Truffle Royale Offline

10K Club
Joined: Jul 2003
Posts: 17,395
Everything, especially denials if that's the reason you denied. Examiners want to verify that the borrower actually had that low of an income.

Return to Top
#1537250 - 04/19/11 02:00 PM Re: Pivot tables and HMDA LARs #12
Kathleen O. Blanchard Offline

10K Club
Kathleen O. Blanchard
Joined: Dec 2000
Posts: 21,293
Correct. Errors in aps not originated are still errors. All data must be correct.

When I sample a LAR I look at all categories.
_________________________
Kathleen O. Blanchard, CRCM "Kaybee"
HMDA/CRA Training/Consulting/Mapping
The HMDA Academy
www.kaybeescomplianceinsights.com

Return to Top
#1537254 - 04/19/11 02:02 PM Re: Pivot tables and HMDA LARs #12
arye23 Offline
Gold Star
Joined: Mar 2011
Posts: 294
If you are using a third party software or the free HMDA DES, you can run the Fed edit checks on there.

You will be able ti identify anything that is a Q24 (or any other edit) that way, and validate it monthly, quarterly, etc.

After that, when you get your response from the Fed after submission, all you have to do is compare lists and respond.
_________________________
CRCM

Return to Top
#1537271 - 04/19/11 02:19 PM Re: Pivot tables and HMDA LARs Kathleen O. Blanchard
Sage Offline
Platinum Poster
Joined: Aug 2005
Posts: 914
So to verify do you contact the loan officer and ask if the data was keyed correctly?

Return to Top
#1537277 - 04/19/11 02:24 PM Re: Pivot tables and HMDA LARs Sage
arye23 Offline
Gold Star
Joined: Mar 2011
Posts: 294
I'd recommend that you pull the file and validate the actual data by reviewing the W-2, tax return, underwriting forms, etc. That's what your examiner is going to do.
_________________________
CRCM

Return to Top
#1538372 - 04/20/11 08:24 PM Re: Pivot tables and HMDA LARs arye23
COMPLIcated Offline
Diamond Poster
Joined: Mar 2003
Posts: 1,035
OK
Can you use the pivot tables with the free HMDA software? We use another vendor and we are not able to pull the information from the LAR into Excel. It is very time consuming for me to manually enter information for analysis.

Return to Top
#1538536 - 04/21/11 12:52 PM Re: Pivot tables and HMDA LARs COMPLIcated
arye23 Offline
Gold Star
Joined: Mar 2011
Posts: 294
You can open the .dat files that your free software or your vendor produces using excel. You will manually have to put the column breaks in, but the data is in LAR-order (Application number, application date, loan type, property type, etc), so it shouldn't be too difficult to put the breaks in. If you need more info or help with this process I'd be glad to walk you through it. Send me a PM
_________________________
CRCM

Return to Top
#1538640 - 04/21/11 02:25 PM Re: Pivot tables and HMDA LARs arye23
Kathleen O. Blanchard Offline

10K Club
Kathleen O. Blanchard
Joined: Dec 2000
Posts: 21,293
Correct - data is validated against loan files, there is no other way and it is what the examiners will do. Re importing from a dat file, it is a very simple process, a normal function within Excel or Access.
_________________________
Kathleen O. Blanchard, CRCM "Kaybee"
HMDA/CRA Training/Consulting/Mapping
The HMDA Academy
www.kaybeescomplianceinsights.com

Return to Top
#1538644 - 04/21/11 02:28 PM Re: Pivot tables and HMDA LARs COMPLIcated
Kathleen O. Blanchard Offline

10K Club
Kathleen O. Blanchard
Joined: Dec 2000
Posts: 21,293
Originally Posted By: COMPLIcated
Can you use the pivot tables with the free HMDA software? We use another vendor and we are not able to pull the information from the LAR into Excel. It is very time consuming for me to manually enter information for analysis.

I have seen several banks have difficulty getting anything other than a pdf file out of their system. In that case, they give me a pdf and I have software that then converts it to Excel with a little bit of effort on my end - and it is little effort. If you have to go that route, let me know and I can tell you what software I use to do that. It is not expensive.
_________________________
Kathleen O. Blanchard, CRCM "Kaybee"
HMDA/CRA Training/Consulting/Mapping
The HMDA Academy
www.kaybeescomplianceinsights.com

Return to Top

Moderator:  SMQ, CRCM