VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status

  • Trying to help some young friends out to better manage finances and (based on their feedback on what would be appealing to them and easier to understand) I'm trying to put together a quick entry tool that allows them to see AT-A-GLANCE CALENDAR view affects of their Income vs Outgo (some is manageable - some are 'unplanned' oh crap--this isn't good scenarios) as we've all experienced that wreaks havoc on the best of budgets..
    This quickly lets them see weeks in advance that on XXX day -- they will be dead broke -- IF they don't take action....


    Attached is a file that holds the CALENDAR view desired (where each day's block shows them INC, OUTGO and running balance)


    * The orig WORKSHEET idea was they wanted to be able to enter "LIKE" things (compartmentalized) made more sense than intimidating "ledger" style..
    * This evolved to WORKSHEET (2) which is more ledger style with all INC and EXP on a single (probably more easily 'extract-able' table) - code wise..
    * Third option was to just throw the ledger table along side the CALENDAR View (on a single worksheet) - which allowed them to see interactivity as they entered things.
    (kept them from having to jump back and forth to another sheet to see the results of their entry)


    The 3rd option is a little more cluttered and of course as months go by, they'll have to do some scrolling to get back to the CALENDAR they want to see but this 3rd option is probably the easiest to code whether using VBA or Functions or combination of both to accomplish auto-fed-summing-status..


    As long as it's functional -- the format option is OPEN for surgery --- whatever will give them the At-A-Glance CALENDAR block VIEW is the ultimate goal!
    I didn't save it as xlsm yet but adding modules and doing so is totally great!
    Thanks in advance!


    NOTE: there's some complication to it -- with "occurrence types" -- like:
    This expense occurs every Monday of every month... and ...
    This income should be applied to the calendar every Quarter (on the 5th of that mo)
    HOWEVER, if it's too complex to mess with auto-feeding those rules in --- They can just enter those manually; feel free to strip out those added columns..

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    .
    Here's my 2 cents worth :lurking:




    My first impression was the sheets are all too cluttered. It is all very good information but showing too much at one time can be a turn off for
    someone trying to learn/understand personal finances for the first time.



    Here is a link to a clean presentation of what they will be working with in real life - a check register and the addition of a bar graph depicting budget amount vs spent amount.


    https://www.amazon.com/clouddr…ef_=cd_ph_share_link_copy

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    If you read my notes, I already advised putting it all on one tab was a bit on the cluttered side - which is why a simple "ledger like" table on one tab and a simple calendar on the other tab was...
    (putting both on one sheet was only an OPTION for possible ease of programming)
    All the note clutter would not be there in real life. (simply notes of what is needed)
    The ledger format they've used in real life and don't like/want that.
    This is what they asked for -- a table to input such as this -- and a calendar layout such as this -- Didn't really ask for impressions or 2 cents -- just some help in accomplishing what was requested. TBNT


    By the way, the graph was already built elsewhere that shows percentage allocations -- it would be added in later (no need for further clutter right) -

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    Hello Christine,


    Just Love your objective ...!!!


    Brilliant idea for quite an ambitious goal ...


    Should Worksheet 1 be the preferred one, why not combining the two subsets to simply have Income and Expenses ...


    By design, if the last two columns are empty you are dealing with a Fixed Date ...otherwise it is a Recurring one ...


    Given your objective of clarity and simplicity, in my opinion, the coding should adapt to the users' wishes ... (even if the consequences are coding constraints...)

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    Thx Carim - Worksheet 1 is totally acceptable the way you've arranged it if we can find a way to get it working/feeding the Calendar...? :eureka:


    (I imagine they'll prefer the INC & EXP separated lk WS1) more than having it all in a single table lk WS2 -- but for ease of programming/lookup/indexing etc - I figured a single table would be the easiest to accomplish (so I'm good w/ either solution).
    Do you see a way to make this layout work?


    As far as there only being 2 categories: Fixed Date or Recurring Dates
    Yes, this is true to a point... However, there's essentially sub-sets with that 2-prong tier..
    --Fixed is easy
    ex1: this INC or EXP amt always occurs on X number day ("10th"), every month


    --Recurring is where is gets a bit hairy --
    ex1: Re-Occurs every Monday (doesn't matter what the number ends up being from month to month it will always change)
    ex2: Re-Occurs every 5th day of a Month (the day will always be changing)
    ex3: Re-Occurs every 3rd month on the 10th of that quarterly month....
    Yes they're each recurring, but each uses a diff variable: specific number date (10th), specific day (Monday), specific interval (Quarterly)

    Yes, I suppose it's still boiling down to FIXED and RECURRING -- but does it make it too difficult to accommodate the variable examples shown?
    If so, I'll just tell them to enter those oddballs manually in their ongoing manual list.. (which would follow any pre-set up Fixed & Recurrings)
    It sounds like you don't foresee it being an issue - if that's the case, GREAT!! Just hoping to come up w/ a close solution.


    Thanks greatly for trying to help with figuring out a solution!
    C.

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    Hello Christine,


    Let's try the small-step approach ... to build your model incrementally ...


    What would be the very first element to code ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    Assuming we eliminate the need for the code to auto-generate the calendar (I'll just hand-build that I guess for now)...
    Assuming that CALENDAR tab is clean and ready for population as-is.... (and already has the running balance formulas in place and protected)
    There's probably a thousand approaches, but I'll take a stab and say start at the beginning of the year: (although this attchmt starts w/ Jun thru Aug)

    1. Perform some sort of lookup/find/index that will start w/ "Jan" (1st mo of a yr) = Jan
    2. Anything = to Jan (and) "1" found in the FIXED number column of the WORKSHEET table (and) = to INCOME table
    ....SUM all "1's" together and paste into CALENDAR cell that holds INCOME
    ....SUM all "2's" together and paste into CALENDAR cell that holds INCOME
    ....continue through 28th, 30th or 31st (however many total days are in that month)
    ....(probably need to have the code look to a cross-refc table to identify how many total days are in that month for that specific year)
    3. Perform exact same steps for the EXPENSE table (looking up all = to X, summing them and pasting them into Calendar cell)


    Is this along the lines you're asking for?
    Using formulas could work - but VBA probably much more reliable - just not sure how to accomplish that (esp w/ the 2nd tier variables) :yikes:
    NOTE: I've attached an updated file - cleaned out a bit for those w/ clutter-phobias :duh:

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    Hello,


    Understand the need to consolidate both Income & Expenses into your Calendar view ...


    But, beforehand, are you settled on a 3 month sliding view ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    No, I plan to pre-build the full 12 mos in advance (or if there's code that will build that out, of course this would be most desirable and save time each year...)
    If so, maybe range cells & generate button tied to code module in far top left of calendar worksheet like: "CALENDAR YR RANGE:" [ 01/01/2017 ] - [12/31/2017] ["GENERATE"] - but this may be considered more time consuming scope-creep-so I'm fine w/ just getting 1 manually built year functional so inputs will sum and feed to correct places without having to manually build in hundreds of functions to each calendar cell..


    The 3-mo was just a small example of what's needed to accomplish as far as input area vs receiving area of content

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    OK then ... so the initial step has to be to generate the calendar automatically ... with the Month selection as THE Variable ...


    Do you agree ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    OK then ... :smile:


    Let me draft you a proposal ... asap ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    Just Love it ...


    Your Orchestra is going completely Nuts ...!!!:jumpupdo::jumpupdo::jumpupdo:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    Hello Christine,


    Attached is a first proposal regarding the calendar ...(and only the calendar ...:wink:)


    Which means both Income and Expenses are still not being taken care of ... :saint:


    Regarding the calendar, a conditional format rule allows to hide/display the balance as a function of the calendar ...


    Please let me have your comments ... and let me know if we are heading in the right direction ...

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    Awesome start for sure!
    *I did some testing starting w/ Jan 2017 through EOY (to see if the 3 mos that are visible rolled properly)... YES


    *Next, I changed the first cal month to Dec to see if the 2nd cal rolled to Jan 2018 but 2nd & 3rd stayed at 2017 (and of course the day alignment was off until I updated the yr block to reflect 2018 on BOTH 2nd and 3rd calendars. Once yr was selected, the 2nd and 3rd calendars updated appropriately. (I imagine I'm telling you something you already know -- but in case you've not had the chance to test it -- perhaps it's value added info)


    * Last, I decided to quickly throw in the rest of a full 12 mo period -- and amazingly -- the drop downs are all tying together - forcing Jan thru Dec to appear all the way down in the MO fields - however, the adjustment of proper days stops working at Calendar 4 down through Calendar 12 (which you are probably saying - DUH to - because that hasn't been set up -- but just saying what I'm seeing - hoping it helps)


    Looks like calendar part 1 is almost a "go"! :congrats::thumbup:
    Can't wait to see summing 'humming'!
    (I've attch'd the updated file)ozgrid.com/forum/core/index.php?attachment/72830/

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    Hello,


    I understand from your update ... you wanted a 12-month rolling view ...


    So have quickly fixed the Year issue ... which now adjusts itself ...


    Note your new numbering in Column G is now dynamic and reflects the Month's number ...


    Feel free to test Calendar as much as possible ...


    Once you validate it, we will move forward ...

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    LOOKS GREAT! Will the SUM all INC for a day and SUM all EXP for a day - then COPY, PASTE to proper cells within each calendar day be possible? Ready to move forward

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    Pleased to hear that first step is behind us ...


    We can move forward to either one of the next two challenges :


    1. Identify the correct Destination cell within the Calendar ...(given its unusual structure ...:wink:)


    AND


    2. Work out all Daily Amounts while following all the Rules for Fixed and all the kinds of Recurring items ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    Lol - yes, unusual structure for "future financial health-monitoring" - but not unusual for a calendar structure =-)
    Essentially the left and right sides of every calendar day will hold the INC and EXP..
    The BOTTOM cell of every calendar day holds the running balance...


    If there's a way to program in a "pattern" combined w/ a lookup to locate the proper calendar range -- then it should be "easy" right? ::D
    First part:
    1. INDEX/SCAN through the INC column of WORKSHEET and SUM up like INCOMES (everything with a "1" + "June" + "2017" add together), etc. (indicating all of these income sources occur on the 1st of JUNE, 2017
    2. repeat the same step for EXP column of WORKSHEET (and place expenses where they belong)


    Part 2:
    1. Find JUNE 2017, THEN
    2. Find MATCHing number for the day that the INCome should hit (which would be "1" in this example) and PASTE the SUM into cell Q122
    3. do the same for EXPenses that occur on the 1st of June....................................................and PASTE the SUM into cell R122
    ...repeat until all INC and EXP items on WORKSHEET have been pasted into the CALENDAR


    Easy-Schmeazzzy right? :drunk:
    It's FRIDAY -- we need to be able to celebrate this accomplishment right??


    THAT SAID -- please see the newly attached file and the RECOMMENDATION to make this much easier! (new sheet w/ example added)ozgrid.com/forum/core/index.php?attachment/72846/