Finding Delinquent Accounts from Excel Database and Posting them to a Separate Sheet

Date Sort Test-New.xlsm

Ok so here is a puzzle for a VBA Guru.. I have no idea how to proceed.. All I have done so far is create a command button that creates a new sheet called status when its clicked. In the Database1 Sheet there are accounts constantly being added and deleted, each row in Database1 is 1 account. I need a VBA Macro that can scan the Database1 sheet and locate any accounts that are delinquent and post some specific info from those accounts on the new status sheet.
In the Database, "Column I" shows the beginning date of each account. Payments are due on a monthly basis and the exact due day is specified in "Column L". The attached sheet shows some fictitious accounts and i manually populated the status sheet to try and show what I need the status sheet to show from the delinquent accounts. Please post any questions and I will try to answer them as best i can to clarify what is needed. Any help is greatly appreciated!

Can you explain how the code would know that those 3 accounts in your example are 30 days late? I see the pink cells in your Database1 but I don't see how those blank cells relate to an account being delinquent, and how delinquent it is.

The blank cells are missed payments, for example John's payment was due on the 10th of each month. Since his contract started on Jan 15, 2016(Database1!I2) his first payment would be due on Feb 10(Database1!L2) which is the 10th of the following month. Thereafter he is due to make a payment on the 10th of each month for the length of his term.(36 months) As you can see John missed his March & April Payments in my example sheet. I should have used last year dates in my sample sheet would have been less confusing. but basically in John's example you would take NOW() and subtract the amount of days till his last payment was due(March 10) and add 30 days for the next month, and thats how many days his account is delinquent. Am i making any sense?

PS> I know I recorded a payment for John in January, this was an oversight on my part while I was making the sample file. Technically his first payment should have been made Feb 10.

Maybe I can make it more clear with a new example.

Austin has a contract that started on Jan 15, 2015
His monthly payment is $100.00
His due day is the 10th of each month
His first payment is due Feb 10, 2015
On Feb 8, 2015 Austin makes his $100.00 payment and his account is in good standing.
His next payment is due March 10, 2015
On March 11, 2015 Austin has still not made his March Payment and his account should now start showing up when the "Status" Sheet is generated. As of March 11, 2015 his account is 1 day delinquent.
On April 11, 2015 he still has not made a payment, his account is now delinquent by 31 days and he owes $200.00 plus any late fees to get his account current. His Account should be showing up on the status sheet whenever its generated until he gets his account current again

Hope this helps!

Just because I asked the initial question, I'll respond here.

For me, this is too much coding to do for free, and on top of that there are still a number of situations that haven't been addressed. Perhaps another user will feel differently.

However, I'm sure there a a multitude of sub $50 accounting software programs out there that will do what you're wanting excel to do.

Anyway, I wish you luck.

Is it doable? Sure. Can it be done for $50? If I was interested, I would charge substantially more than that, but a final bid would come only after a list of situations/questions was addressed.

Just as an example (I don't need answers, just meant to show you that a lot stuff needs to be fleshed out):

• Where is that $200 plus late fees coming from? Is it always $200? If not, what determines the number? Where are the late fees listed?
• Is the account still delinquent if the balance is paid, but not the late fees?
• Can a client pay double one month, then not be considered delinquent the next month? See, now we're talking about more than just looking for blank cells, we'd need to calculate expected balance vs. actual balance, etc. More code.
• If the client didn't pay last months balance, but did pay this months, are they 1 day or 31 days delinquent? How does this affect the late fees? How are the feeds affected if the same thing happens the next month?

And so on. There's just a ton of things to consider, things that I'm sure accounting software has already accounted (heh) for. Thus my suggestion.

The closest software that does what I want retails for over $2000.00

Perhaps, and I'm sure you know more than me about accounting software, but just a glance at QuickenBooks webpage shows a listed feature as: "flag late payments & send reminders for overdue invoices". Anyway,

your questions are completely valid and have simple answers.

This is great news! I would suggest compiling answers for all of them, and additionally all other scenarios that you can think of, and posting them so that someone willing to tackle this project has a good idea of the scope and complexity of it.

Again, best of luck.

You have a really bad data structure, but one thing that you could consider is pre-filling your data range with formulas. For example, on your Database1 sheet, in cell X2, use this formula (based on your example date)

=IF( DATE (YEAR ($I2), 1+ INT( ( COLUMN() - COLUMN( $X2) ) /3), DAY( $I2) ) < DATE( 2015, 6, 1), "Delinquent", "")

You could expand that to include a cut-off date when the payment is no longer needed....(But in reality, use the current date, or a date in a cell that you can update to a date of interest

=IF( DATE (YEAR ($I2), 1+ INT( ( COLUMN() - COLUMN( $X2) ) /3), DAY( $I2) ) < TODAY(), "Delinquent", "")

Leave Y2 and Z2 empty, then copy X2:Z2 and paste for all the three column monthly groups. When a payment is made, the formula is overwritten, and the "Delinquint" will go away.

Use CF for all your rows using the Formula option, with the formula

Formula:

Please Login or Register  to view this content.

to highlight entire rows that have one or more delinquent entries.

Note that I had to insert a bunch of spaces into my formulas to get them to post here - not sure what the software disliked, but.....

You can do a lot with helper formulas and database extraction formulas. For example, the status sheet is now all formulas that pull delinquent accounts and only delinquent accounts. But not all the values are calculated - but this will show you what you can do with helper formulas. You just need to match the headers to allow the values to be pulled into the status sheet. I wasn't sure how you handle multiple years, and I used a basis date in cell E1, and that date should always be earlier than the values you are entering until you are done, at which point that should change to show which accounts are delinquent.

Date Sort Test-New.xlsx

All the formulas that i used can be added to the far right of the database sheet, or even another sheet. The idea is only to flag the values of interest, and to extract the numbers that you need based on what you have. And there really is no need to clear out years of data - Excel can have 16K+ of columns, enough for about 500 years given how you use three columns per month.

What you can do is remove accounts (rows) as they are closed, and hide years when they are so old that there are no payments left to be made from those years. But there is no need to start over.