Also, when you choose the what does the your Cash Flow Categories show on the âSingle Investmentâ sheet?
And, the revision should be 1.6.
Thanks.
@aaron18
I looked into this more and think I know what the problem is. If your total # of cells in your original Transactions sheet is greater than ~200k (Google doesnât seem to give an exact # so Iâm guessing here), the importrange function for Transactions will give a âresults are too largeâ even if your result is a subset of the import range. There are some ways to work around this.
Give me a few days to post an update.
Thanks.
Here is the link to a newer revision- Investment Returns (Rev 1.7) - Google Sheets. Please make a copy, link to your main Tiller sheet, and see if this loads correctly.
FYI- I broke up the importrange functions into smaller blocks to avoid the âResults too largeâ error. I did this for the Transactions and Balance history sheets as those were the most likely to have an error. The Transaction sheet will query the first 60,000 rows of data and the Balance History sheet will query up to 90,000 rows.
Let me know if this solves your problem.
Thanks.
Yep - That did it. Thank you!
(Interestingly, the header columns are still saying âError Result too largeâ even though they seem to be pulling in the appropriate transactions. I previously pulled a bunch of transaction data from my old Mint account, and Iâm curious if something in the tagging is causing the error.)
Great that fixed your problem.
I believe that error message should go away. Maybe try closing an re-opening? If it remains, please send me a screenshot.
I just set up the sheet for the first time and am receiving the same error as above. Screenshot below. My transactions sheet has less than 17k rows. My Balance sheet has a little over 12k rows. I am going through the instructions and not everything is clear. I feel like I am missing something.
Also, none of my asset accounts are showing up. I linked my tiller sheet URL correctly in the Tiller Connection sheet, but nothing populates. I am unsure if it has anything to do with my transactions not fully populating. As shown below, everything populates correctly in the Tiller Connection sheet except the Super Category as I donât have that in my solutions.
I did choose my cashflow accounts in the Single Investments sheet. I have an investments balance and crypto balance that categorizes any transaction from any investment account such as any positive and negative transactions for those particular accounts. Those are categorized as transfers and both negative and positive transactions are just under balance. I also have an investment/crypto deposit and investment/crypto withdrawals which categorize transactions between my investment accounts and the checking account. Those are categorized also as transfers. These deposit/withdrawal and balance are pretty much duplicates, but it helps me know which originates from what account at an initial glance. But although I am able to choose the balance as well as the investment deposits as cash flows, the investment withdrawals are not an option to choose from. These are all categorized as transfers like I stated above because I categorize any buy and sell transactions as actual expenses/income. Hope all that makes sense.
The foundation sheets headers populate correctly as shown below.
Sorry for the delay; have been out for a bit.
It looks like it is linking correctly.
Which tab Is your screenshot showing the error? The âAccountsâ and âCategoriesâ tabs should populate pretty quickly as there is not much data in those. Depending on the number of items, the âTransactionsâ and âBalance Historyâ tabs can take a while, sometimes 10-15 minutes on my M1 Macbook Air with 16GB RAM with a fiber internet connection. I have noticed older machines or slower internet connects will take longer. FYI- I get the same error as you see on these 2 tabs, but it eventually loads the data. I usually leave the sheet open in a browser tab so it consistently updates.
Make sure you are using the most recent revision (1.6) as this includes the fix of too large of ImportRange.
I am curious- why do you would have a "⌠Balance " transaction as a Cash Flow category? You only want to include transactions that withdrawal into or deposit from external accounts. Any transactions that are internal to the investment account (e.g. fees, dividends, realized gains, etc.) should not be included as they are comprehended in the balance.
Let me know how else I can help.
1 Like
My Category, Accounts, and Balance History tabs show no errors. The Transactions tab is the only one showing errors. I believe that is due to the data not fully being loaded like you mention but could also be related to other errors that I am experiencing on other tabs.
The screenshot is of my Transactions tab. At the time of the screenshot, the entire sheet was blank, but I guess like you stated above, it will take a while. I am in the process of getting my beefy laptop fixed so I wonât worry at the moment about everything loaded as the computers that I am using do not have a lot of processing power.
I am using version Rev 1.7. That should be good right?
I may do a whole rework of how I categorize my transactions related to all my investments as I wasnât completely sure the best way to categorize everything when I initially started using Tiller. I may not. I just have to figure out if I am doing it correctly. Iâll give an example of how one of my transactions goes.
- I transfer 200 from my chase account to my Charles Schwab checking account. This negative transaction under my Chase account is categorized as a transfer. Specifically, Transfer to Myself to differentiate it from a transfer to/from other people since I make a lot of transfers between my various accounts because I donât keep money in accounts with low interest rates.
- Then I transfer the 200 from my Schwab Checking to my Schwab Brokers account and categorize this negative transaction as Investment Deposit. That negative transfer shows up under my Schwab Checking account.
- Now another transfer under Schwab Brokers shows up and I categorize that positive transaction as Investment Balance. Any transaction under any of my brokers accounts (positive or negative) to/from checking accounts are categorized as Investment Balance so I know its dealing with a brokers account as opposed to a checking account.
- As a side note, when I transfer from my Scwhab brokers account to my Schwab checking account I categorize this negative transaction as Investment Withdrawal. All these are categorized as transfers in the categories sheet.
- Now my buy and sell orders are pulled in as separate transactions. When the 200 dollars are deposited into my brokers account any purchases are broken up into x amount of share purchases for x amount of different stocks. Each of those purchases/sells will appear as a separate transaction. To avoid confusing myself, especially when trying to figure out the best way to budget hands on like Tiller allows as opposed to something like Mint who does it for you, I categorize my stock purchases as Investment: Purchase and my stock sells as Investment: Sell. These both are categorized as expenses and incomes on the categories sheet.
- Any dividends I receive on any stocks are received as a separate transaction every month per investment account. I have them categorized as Investment: Dividends. These are positive transactions and are categorized as income. For any accounts that have automatic dividend reinvestments, these negative transactions are categorized as an expense and Investment: Reinvested Dividends.
I realize that I could and very well may merge Investment Deposits, Withdrawals, and Balance into just one transfer category but at the moment it allows me to break up multiple transactions and I know on quick glance which transactions are from a checking account or a brokers account without needing to glance at the account it falls under.
I have an exact set up for all my crypto purchases to keep those separate too. I am a huge procrastinator and have had my investment budgeting implementation on Tiller sitting on the backburner for over a year now. I have been using share sight instead to track my investments but that isnât as automatic and hands off and accurate as I would like it to be.
Some things to unpack hereâŚ
Rev 1.7 is fine.
I donât see a screenshot of your Transactions tab, but it sounds like it finally populated. I used to use a very old iMac and filling in the data took a long time. Is the Transactions tab now showing data? If not, nothing will work.
If it is, it will then take a bit to do the calculations.
You said your Asset accounts show up in the âAccountsâ tab, correct? If so, It is simply a lag in doing the calculations. To see if this is the issue, one thing you could is make a copy of the workbook, relink the copy to your main Tiller workbook, and delete the âAll Investmentsâ tab as thatâs the most processor intensive. If you do that and the data start populating, processor power is your issue.
As for what transaction categories you want to use for âInvestment Cash Flowâ, you only want to choose categories that deposit or withdrawal from the investment account to external accounts.
From your descriptionsâŚ
This is an Investment Deposit. You can call it whatever you like, but I don;t know why this would be referred to as a âBalanceâ
And this oneâŚ
Is an Investment Withdrawal.
From your descriptions, these are the only 2 categories that should be chosen as âInvestment Cash Flowâ.
One last commentâŚ
Calling a negative transaction a âDepositâ makes no sense to me. I always think of deposits as a positive transaction. You can call it whatever you like, but for sure, this one should not be included as an âInvestment Cash Flowâ.
1 Like
My transactions tab has not populated. I finally got my gaming laptop fixed and it has enough processing power where loading shouldnât be an issue. I am still receiving the error âResults too large.â Thatâs after letting it sit and download for more than half an hour. Is there a formula that can be edited if we just have too many transactions for it to cipher through or pull in? I donât know any other reason I would be getting this error other than it just being too much data to pull in because at first, it states Error: Loading Data while it loads everything. Then finally gives the error above.
Please take a screenshot of the formula in A1 of the Transaction sheet.
The start of the formula should look like thisâŚ
You should not be getting âResult too largeâ with Rev 1.7. The formula breaks up the ImportRange into chunks of 15,000 cells to ensure it doesnât give that error. It will import 60,000 total Transactions that match your Cash Flow Categories from the Single Investment sheet.
Copied the formula in cell A1
={query(importrange('Tiller Connection'!$A$2,"'Transactions'!A1:Z15000"),"Select Col"&Column(indirect('Tiller Connection'!D8&1))&" Where Col"&Column(indirect('Tiller Connection'!C6&1))&" = '"&'Single Investment'!$H$1&"'"&IF(isblank('Single Investment'!$H$2),""," OR Col"&Column(indirect('Tiller Connection'!C6&1))&" = '"&'Single Investment'!$H$2&"'")&IF(isblank('Single Investment'!$H$3),""," OR Col"&Column(indirect('Tiller Connection'!C6&1))&" = '"&'Single Investment'!$H$3&"'")&IF(isblank('Single Investment'!$H$4),""," OR Col"&Column(indirect('Tiller Connection'!C6&1))&" = '"&'Single Investment'!$H$4&"'")&IF(isblank('Single Investment'!$H$5),""," OR Col"&Column(indirect('Tiller Connection'!C6&1))&" = '"&'Single Investment'!$H$5&"'"),1);
iferror(query(importrange('Tiller Connection'!$A$2,"'Transactions'!A15001:Z30000"),"Select Col"&Column(indirect('Tiller Connection'!D8&1))&" Where Col"&Column(indirect('Tiller Connection'!C6&1))&" = '"&'Single Investment'!$H$1&"'"&IF(isblank('Single Investment'!$H$2),""," OR Col"&Column(indirect('Tiller Connection'!C6&1))&" = '"&'Single Investment'!$H$2&"'")&IF(isblank('Single Investment'!$H$3),""," OR Col"&Column(indirect('Tiller Connection'!C6&1))&" = '"&'Single Investment'!$H$3&"'")&IF(isblank('Single Investment'!$H$4),""," OR Col"&Column(indirect('Tiller Connection'!C6&1))&" = '"&'Single Investment'!$H$4&"'")&IF(isblank('Single Investment'!$H$5),""," OR Col"&Column(indirect('Tiller Connection'!C6&1))&" = '"&'Single Investment'!$H$5&"'"),1),"");
iferror(query(importrange('Tiller Connection'!$A$2,"'Transactions'!A30001:Z45000"),"Select Col"&Column(indirect('Tiller Connection'!D8&1))&" Where Col"&Column(indirect('Tiller Connection'!C6&1))&" = '"&'Single Investment'!$H$1&"'"&IF(isblank('Single Investment'!$H$2),""," OR Col"&Column(indirect('Tiller Connection'!C6&1))&" = '"&'Single Investment'!$H$2&"'")&IF(isblank('Single Investment'!$H$3),""," OR Col"&Column(indirect('Tiller Connection'!C6&1))&" = '"&'Single Investment'!$H$3&"'")&IF(isblank('Single Investment'!$H$4),""," OR Col"&Column(indirect('Tiller Connection'!C6&1))&" = '"&'Single Investment'!$H$4&"'")&IF(isblank('Single Investment'!$H$5),""," OR Col"&Column(indirect('Tiller Connection'!C6&1))&" = '"&'Single Investment'!$H$5&"'"),1),"");
iferror(query(importrange('Tiller Connection'!$A$2,"'Transactions'!A45001:Z60000"),"Select Col"&Column(indirect('Tiller Connection'!D8&1))&" Where Col"&Column(indirect('Tiller Connection'!C6&1))&" = '"&'Single Investment'!$H$1&"'"&IF(isblank('Single Investment'!$H$2),""," OR Col"&Column(indirect('Tiller Connection'!C6&1))&" = '"&'Single Investment'!$H$2&"'")&IF(isblank('Single Investment'!$H$3),""," OR Col"&Column(indirect('Tiller Connection'!C6&1))&" = '"&'Single Investment'!$H$3&"'")&IF(isblank('Single Investment'!$H$4),""," OR Col"&Column(indirect('Tiller Connection'!C6&1))&" = '"&'Single Investment'!$H$4&"'")&IF(isblank('Single Investment'!$H$5),""," OR Col"&Column(indirect('Tiller Connection'!C6&1))&" = '"&'Single Investment'!$H$5&"'"),1),"")}
Included Screenshot
1 Like
Love this tool - looks like it has a lot of potential benefit for me! Thanks for all the work putting this together. Iâm trying to get the template set up (Rev 1.7), but for some reason the âEOM Balanceâ in every sheet (Single Investment, All Investments, Monthly Investments) all populate as the CASH FLOW value for the first month the account has data, instead of pulling the EOM Balance for the appropriate Month. Any idea why that might be happening? Trying to attach a screenshot of this from the âMonthly Investmentâ sheet. In the âhiddenâ columns, I see this same issue - column V shows the same value every month. But over in columns BG:BK I can see my accounts with the correct Balances and Dates pulled from my main Tiller sheet. But I guess the formula isnât finding it?
Hoping there is a quick fix, because Iâm excited to get this tool working for me!
1 Like
@clkincaid ,
Thanks for sending.
Keep in mindâŚ
What if I donât have a Balance for each month?
If it is the first Transaction month of the account, Balance = the initial deposit.
If it is not the first Transaction month, Balance = previous monthâs balance
Looks like the workbook canât find balances for this account so it is using the previous monthâs balance (which was your initial deposit).
The monthly balance is pulled from the imported Balance History. Please check the imported Balance History tab to see if you have balances for this account. If there are none, itâs either a connection error or there arenât any balances in your foundation Tiller workbook.
If there are monthly balances in BG:BK, they should then show up in Columns DF:DS. Please check those as well.
1 Like
Thanks for responding! I think I may have actually reworked part of the formula in Columns DF:DS to get this to show my Balances correctly, so feel free to disregard my question if you donât have the time to engage further.
But, your response above is what I expected to happen, and thatâs what had me confused actually â I DO see monthly balances in columns BG:BK for my account. There is at least one balance for every month since the initial transaction. But those are not showing up in column DF â it instead shows the initial deposit, then repeats that for subsequent months.
I dug into the formula a bit, and think I found the piece that is supposed to find the Balance in columns BG:BK: iferror(index(query({$BG:$BG,$BH:$BH,$BI:$BI,$BJ:$BJ},âSelect Col3 Where Col2 = DATE 'â& text($DD3,âyyyy-mm-ddâ)&ââ AND Col4 = 'â&DF$2&ââ ORDER BY Col1 DESCâ),2,1),if(istext(DF2),BO3, DF2))). It seems like for some reason that is NOT finding the balances in those columns, so it throws and error and pastes the previous Monthâs balance instead.
Iâm not familiar with how the âqueryâ function works, so I didnât know how to troubleshoot that syntaxâŚinstead I reworked that section of the formula as an Index-Match to find my balance in the appropriate columns, and that seems to have worked for me: iferror(index({$BG:$BG,$BH:$BH,$BI:$BI,$BJ:$BJ}, MATCH(1,(DF$2=$BJ:$BJ)*($DD3=$BH:$BH),0), 3).
I have to make this edit in the other 2 sheets as well, but now everything seems to be working! Great tool!
1 Like
If you got it to work, great. I would like understand why it wasnât working.
If the correct balances are in BG:BK, they should be organized into months in columns DD:ES. From your description, it sounds like the breakdown was in the formulas in DF3 through ES497. If I remember right, I used Query because you could have multiple balances for a month and I wanted to pick the balance closest to the end of month. The âDESCâ keyword in Query sorts them by date inside the month and I pick the latest balance. How would Index Match do this?
As for the Monthly balance always showing the initial deposit, that is how itâs designed (right or wrong).
When Index Match searches in an Array, it locates the first instance that matches the specified criteria. So in the âAll Investmentsâ and âMonthly Investmentsâ, the dates in columns with the Balances are in Newest to Oldest â so when Index Match finds a Balance with a November date (for example), it is finding the latest date in November that pulled a Balance. âSingle Investmentsâ has the dates in Oldest to Newest order, so I used âxmatchâ which works the same, but lets you specify that you want to find the last item that matches your criteria.
1 Like
Thanks, that makes sense.
Not sure why the Query didnât work, but sounds like you got it working now, which s great.
1 Like