Thread Options
|
#728347 - 05/08/07 06:03 PM
Pivot tables and HMDA LARs
|
Power Poster
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
|
|
|
|
#729661 - 05/10/07 02:14 PM
Re: Pivot tables and HMDA LARs
Truffle Royale
|
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
|
|
|
|
#1537228 - 04/19/11 01:39 PM
Re: Pivot tables and HMDA LARs
Truffle Royale
|
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
|
|
|
|
#1537245 - 04/19/11 01:58 PM
Re: Pivot tables and HMDA LARs
Kathleen O. Blanchard
|
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
|
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
|
|
|
|
#1537254 - 04/19/11 02:02 PM
Re: Pivot tables and HMDA LARs
#12
|
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
|
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
|
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
|
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
|
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
|
|
|
|
|
|