Connected T Accounts

  • Hello World!


    I'm not sure if this would fall under formulas or VBA, but here's my best attempt at explaining what is going on:


    My employer tracks investment cashflows using basic t-accounts. We are still going to input cashflows using independent t-accounts for each investment, but we now want all that data to automatically flow into a "master chart"


    If you image, Investment A pays a dividend of $100 on November 1... I'll input that on the T Account (which is a unique worksheet) for Investment A. Investment B pays a $25 dividend on November 4, the same procedure is triggered: I input that activity on a t-account for Investment B. If I print either worksheet (for investments a or b) it will only show data for THAT particular investment. But I also need a "Master Account" which automatically collects new lines from its "child tables" and copies them to the next available line on the Master Chart. The idea is to not be required to enter the same data twice, and thereby avoid human-error in doing so.


    Additionally, there will be a constant influx of new investments with their own t-charts so whatever the tool is, be it formula or VBA, needs to be relatively easily "reprogrammed" to look at new worksheets.


    Lastly, I *think* I want to accomplish this with a pivot table, which would allow me to slice and dice the data in the future on an ad hoc basis... but I don't know how to get the data from the individual t-accounts to flow into a table.


    Attached is a sample mock-up of what I'm trying to get to. Any help is much appreciated!

  • I would say that your T-Accounts are not necessary. maintain all the data in the Master Table and then use the PivotTable that I have added. It may need some tweaking to fully suit your needs, but it will be more dynamic than T-Accounts.


    Check it out and let me know if you want more help with it.

  • I would say that your T-Accounts are not necessary. maintain all the data in the Master Table and then use the PivotTable that I have added. It may need some tweaking to fully suit your needs, but it will be more dynamic than T-Accounts.


    Check it out and let me know if you want more help with it.

  • Hello,


    Do not know if the overall structure is imposed on you or not ...


    Things could be done in a much simpler way ...


    Meanwhile ... in your Admin sheet ...take a look at Column E ... with new formulas ...


    Hope this will help

    Files

    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...:)

  • Hi royUK!


    Thank you for the help! I do need to maintain the individual T-Accounts because that's basically what management wants, lol. But in all honesty it does help us more directly track unfunded commitment, paid-in capital, and the call as a % of commitment. This could be accomplished with a slick new pivot table and VBA and kinda have it setup as a form/report but I've tried implementations like that and it honestly just scares off management because they're so unfamiliar with those kinds of tools. So I've resolved to try to keep it "low tech" and more familiar for them, which is an entirely different set of challenges.

  • Seems to me ... among the first steps ... there is the need to create a Index for all your funds ...


    You could test following macro :



    Hope this will help

    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...:)

  • Hello again,


    What are precisely the rules for your ' Master Table ' ...


    Is it only a recap of all entries sorted on date (column D ) ...?

    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...:)

  • Hi Carim,


    That's a nifty bit of code! In the full version I created a table of contents that hyperlinks to the desired tab (there are 66 existing t-accounts, with more to come), which just made it easier to get to the correct tab to input accounting entries.


    But to answer your question, yes, it is simply a recap all the entries from the "child" t-accounts. With the headers filtering turned on, yes I will most likely have them sorted by date, but I might also want to sort by "transaction description" and gather up all the Capital Calls for a given period, for example. On the newly attached file (which includes your VBA), you can see I added an additional attribute column on the Master Table.

  • Glad this could help ...:wink:


    As suggested in your Admin sheet ... Totals should appear in Row 3 ... Above your Table ...


    in order to have both formulas ( for Committed and Callable ) dynamically adjusting themselves ... as the Table grows ...

    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...:)

  • Attached is your Version 2 test file ...


    Just add a few fake lines in your sheets ' Investment A ' and ' Investment B ' ...


    and then Click on Button ' Master '


    You are left with the Sorting issue ... as needed ...


    Hope this will help

    Files

    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...:)

  • [h=1][/h] Thanks Carim!


    Two questions:
    -I'd like to thank you by sending a CashApp payment so if you want to direct message it to me or share it here, look for a cash thank you from me!
    -Would it be smart to put the "Index Funds" button on the Admin tab and let it populate the names directly onto the table? The Indirect formula would then automatically pickup the 'Committed' and 'Callable' amounts?

  • You are Welcome ... :smile:


    Regarding your two questions :


    1. You have not posted your request in the HIRE HELP section ... so, by design, assistance is obviously totally free :wink:


    2. You are right regarding the idea of merging all operations into a single macro which will generate both Tables in the ' Master Table ' and the ' Admin ' worksheets ...


    As soon I have a moment ... will prepare this modification ...

    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...:)

  • Hello Robert,


    In order to fully automate both your reports, would suggest to have in each ' Investment ' tab both the Vintage and the Strategy type ...


    Do not know if your Management will allow you to modify / improve the template ...


    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...:)

  • Should your Management allow you to slightly amend the Investment Template ...


    Attached is your Version 3 file with a few modifications:


    1. Template is holding all data required both for Master and Admin


    2. Index macro also generates your Admin table


    3. Master macro only retrieves the newly added records and has a built-in sort feature


    Hope this will help

    Files

    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...:)

  • Thanks Carim!


    First, lets not call it 'compensation,' and just a "tip." For real, drop you CashApp handle.


    It works amazingly!


    Last thought:


    There's an "Investments List" on the Master Table worksheet that hyperlinks to each respective fund-specific t-account. But these are the same as the Funds Table on the Admin worksheet. Would it be possible to just have the Funds Table on the Admin worksheet have the Names hyperlinked, cutting down on redundancy?

  • You are welcome :smile:


    Thanks a lot for your Thanks ... :thumbcoo:


    Your last thought makes a lot of sense ...!!!


    Will prepare Version 4 with your suggested modification ...

    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...:)