I have a budget forecast workbook where I track my upcoming recurring bills along with all my transactions - be it cash, ATM, check, etc. This also includes any deposits. It is basically an electronic check register with a budget forecast function. There are three ways I gather/enter data:
1) Manually type a transaction into the spreadsheet
2) Download a CSV copy of my bank statement from their website into a separate sheet tab and then copy/paste line items into their respective paydate blocks based on transaction dates
3) I have a separate sheet tab listing my recurring bills with their due dates which I then manually enter into their respective paydate blocks based on due dates (in layman's terms: pay this bill with this check before the actual due date so that the bill isn't late...like the Price Is Right: closest without going over).
The main spreadsheet is a template comprised of repeating blocks (or sections). Each section is essentially a mini-form that captures my financial transactions within a block of time that spans two weeks since I get paid bi-monthly. So, at the top of each block is a date. Example: the very first block would have "June 7, 2019" manually entered in cell B2. Then the formula for the date in the next block would read =B2+14...and so on.
Each block builds off the data from the one above it...like a check register.
Here is what I am struggling with:
For years I have been entering the data manually (see points 2 and 3 above). What I would like to be able to do is this:
Regarding Point #2 :
Download a CSV copy of my bank statement from their website into a static sheet tab and then run a macro to insert the new line items into their respective paydate blocks based on transaction dates.
EXAMPLE: I get paid on June 7 and again on June 21. On June 15th I bought $40 in gasoline. Therefore, this transaction should appear in the June 7 paydate block.
Regarding Point #3 :
Automatically enter my recurring bills into their respective paydate blocks based on their due dates.
EXAMPLE: If my cell phone bill is due on the 1st of the month, find the paydate block PRIOR to the recurring bill's due date and enter the record into that paydate block.
I am thinking this can be accomplished through some sort of array function, but I have to admit, arrays are my kryptonite!
1) Manually type a transaction into the spreadsheet
2) Download a CSV copy of my bank statement from their website into a separate sheet tab and then copy/paste line items into their respective paydate blocks based on transaction dates
3) I have a separate sheet tab listing my recurring bills with their due dates which I then manually enter into their respective paydate blocks based on due dates (in layman's terms: pay this bill with this check before the actual due date so that the bill isn't late...like the Price Is Right: closest without going over).
The main spreadsheet is a template comprised of repeating blocks (or sections). Each section is essentially a mini-form that captures my financial transactions within a block of time that spans two weeks since I get paid bi-monthly. So, at the top of each block is a date. Example: the very first block would have "June 7, 2019" manually entered in cell B2. Then the formula for the date in the next block would read =B2+14...and so on.
Each block builds off the data from the one above it...like a check register.
Here is what I am struggling with:
For years I have been entering the data manually (see points 2 and 3 above). What I would like to be able to do is this:
Regarding Point #2 :
Download a CSV copy of my bank statement from their website into a static sheet tab and then run a macro to insert the new line items into their respective paydate blocks based on transaction dates.
EXAMPLE: I get paid on June 7 and again on June 21. On June 15th I bought $40 in gasoline. Therefore, this transaction should appear in the June 7 paydate block.
Regarding Point #3 :
Automatically enter my recurring bills into their respective paydate blocks based on their due dates.
EXAMPLE: If my cell phone bill is due on the 1st of the month, find the paydate block PRIOR to the recurring bill's due date and enter the record into that paydate block.
I am thinking this can be accomplished through some sort of array function, but I have to admit, arrays are my kryptonite!
