Quicken Transaction file size getting too big, register difficult to scroll

PER
PER Member ✭✭
I'm running Quicken Premier with the Version R32.10 Build 27.1.32.10 on Windows 10. I have multiple investment accounts that create hundreds of transactions each month due to dividend payments. After a couple of years now on this file the size is likely getting to big and Quicken simply can't scroll up or down through my investment accounts easily....it starts stepping one transaction at a time, increment, increment, and very sluggish. Is there someway to cleanly set a backup period to archive and move forward with a smaller size file? When I have dealt with this in the past the answer was no, as the archive still kept the high transaction. I really hate having to start a clean new Quicken file every few years which then only goes back 90 days and takes lots of effort to get back to an accurate picture of today.

Comments

  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    The size of you data file isn't the problem.  Quicken is a database program, the access is "direct" skipping over data it doesn't need to read.

    There isn't a system to archive investment transactions to a different file.
    The main reason they don't do that is because it would extremely complicated to make sure that they have the information they need for the security lots for securities that aren't completely closed out.  But there is a method to archive transactions for closed to another account.

    The truth is that Quicken was never designed for the high activity trading that you are doing, so yes it isn't surprising that you are running into performance problems in those accounts.  This has more to do with how they are scanning the security lots for the information for the register, and the register GUI itself than fetching the data from the data file.

    Quicken doesn't load in accounts until you open them.  So if you move transactions to an account that you aren't going to regularly open that might help.

    Select the gear icon in the upper right of the register and select "Archive Transactions".
    Signature:
    This is my website: http://www.quicknperlwiz.com/
  • PER
    PER Member ✭✭
    Your comment that Quicken was not designed for high activity trading is not an accurate representation of my situation. I am a buy and hold investor that holds a well diversified account. Each position pays either a quarterly or monthly dividend. So just holding positions creates transactions as they pay dividends. My file dates back to 2017, so perhaps by now in four accounts there might be 10,000 transactions and those investment accounts are very sluggish.

    Is the only recourse to create a backup copy, file it, and then start a new file which means setting up each account within Quicken? That really is not an acceptable choice. I've used Quicken for over 30 years and would expect some path to be able to handle this better. Starting a new file from scratch involves setting up each of 14 various accounts and then dealing with placeholder problems for 4 investment accounts that don't have any prior basis knowledge for holdings.
  • NotACPA
    NotACPA SuperUser ✭✭✭✭✭
    edited March 2021
    @PER you stated "hundreds of transactions each month due to dividend payments."
    That suggests a minimum of 300 different securities (since few securities except bond funds pay monthly).  Most securities pay quarterly, semi-annually, or annually.

    Q is simply NOT the best product for you.  It was never designed/intended to handle that kind of investment volume.

    Q user since February, 1990. DOS Version 4
    Now running Quicken Windows Subscription, Business & Personal
    Retired "Certified Information Systems Auditor" & Bank Audit VP

  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    You might think of "high activity trading" as "day trader" or something like that, but that isn't what I meant by it.  "activity" is judged not on what kind of trader you are, but on the number of transactions per month, and you have a high amount of them plain and simple.

    PER said:
    That really is not an acceptable choice. I've used Quicken for over 30 years and would expect some path to be able to handle this better.

    I always wonder about statements like this, as in what people expect, especially when one is told that what they are doing isn't what Quicken/the tool they are using isn't designed for it.

    Quicken isn't going to magically change into an "acceptable choice".   You can try the workaround I suggested and see if it helps, otherwise if it isn't acceptable I think you should be looking for something that is acceptable to you.
    Signature:
    This is my website: http://www.quicknperlwiz.com/
  • q_lurker
    q_lurker SuperUser ✭✭✭✭✭
    You can configure the account list view (Ctrl-A) to show number of transactions in the account.  No need to guess or estimate.  How many?

    For most users, a 'large transaction number' investment account behaves poorly for transaction entry.  It may perform poorly for scrolling, but that is a less common complaint.  Other accounts do not show that same performance issue.  Is that your case - some ok, some not?  Only investment account show the problem?

    In my case, I relate the performance issue mostly to number of transactions.  Chris has put forth elsewhere that number of securities and number of lots of those securities are also significant factors.  I have no argument with that position. 

    What I have done as performance in a single account has degraded to below my threshold, I create a new version of that account.  I then use Shares Transferred 'move' the shares from the old (slow) account to the new (empty) account.  All security positions are thus maintained.  The old account can be turned off from downloading and that feature activated for the new account.  I typically do that at the beginning of a calendar year.  Thus I might have Inv-Acct before 2010, Inv-Acct 2011+, Inv-Acct 2015+, etc.  This is perhaps comparable to the "archive" function but I find it more sensible (to me).   


  • markus1957
    markus1957 SuperUser, Windows Beta Beta
    edited March 2021
    You don't see this behavior in a spending account because Quicken has indexed the scroll function.  It's been suggested for years that investment accounts should also be indexed to improve performance when scrolling thru the register table.

    adding- I did notice a substantial register scrolling improvement on my newer PC with an 8th generation i3 core and 13Gb Optane hybrid drive.  The processor is low end so I'm attributing the performance boost to the SSD portion of the hard drive. 

    My largest transaction count in an investment register is only about 6K; the lag is a transactions per register issue, not transactions per file.
  • UKR
    UKR SuperUser ✭✭✭✭✭

    1) If you haven't done so, I'd recommend the database maintenance procedure to copy, validate (the copy) and supervalidate (the copy) as described in part (2) of Troubleshooting 101 - Fixing Software Installation and Data File Problems

    While running the Validate step, together with the Validate File option select the options to Rebuild investing lots and consider Correct investing price history. If you hold many securities long-term, you will have many daily price history records per security. This may contribute to slow performance due to the sheer number of records.

    When done, work with the copied and validated file to see if performance improved.

    2) Do make sure that there are no Placeholder transactions in any of your investment registers.

    About Placeholders (Quicken Windows)

    "N/A" in certain fields of an investment transaction indicates that you have Placeholder transactions in your account that need to be deleted and replaced with the correct Buy / Sell / etc. transaction that should have been recorded here.
    For more about Placeholders please go into Quicken Help (press the F1 key from anywhere in Quicken) and perform a Contents search for "placeholder".
    Alternatively or in addition to the above, read the first few articles found here: https://www.quicken.com/support/search/placeholder

    3) If you're an active trader and your current investment account has many transactions pertaining to sold securities you may want to consider this:

    Archive Investment Transactions

    For about a year now, a new function has been available in Quicken, Archive Transactions. It will split your investment account register into two parts, leaving only those securities' transactions where you currently hold >0 shares in the active register. The rest is moved to a new Archive register within the current data file.

    Having less transactions in your active register might improve performance.

    Please read and carefully follow instructions here: https://www.quicken.com/support/how-archive-investment-transactions
    Eventually, after a couple of years, you might consider splitting your data file in two, with all the archived investment transactions in the old file and none remaining in the active file.




  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    You don't see this behavior in a spending account because Quicken has indexed the scroll function.  It's been suggested for years that investment accounts should also be indexed to improve performance when scrolling thru the register table.

    adding- I did notice a substantial register scrolling improvement on my newer PC with an 8th generation i3 core and 13Gb Optane hybrid drive.  The processor is low end so I'm attributing the performance boost to the SSD portion of the hard drive. 

    My largest transaction count in an investment register is only about 6K; the lag is a transactions per register issue, not transactions per file.
    I don't think people understand how the investment accounts are setup, and why yours and others suggestions haven't been implemented.

    The "indexing" you are talking about is in fact "grid virtualization".  As in GUI controls are actually very inefficient.  After just a few hundred rows they start to drag systems to their knees.  So what is done is to only have the controls enough for the rows that are visible.  And as you scroll then reuse the same controls just changing the data.

    The first road block for this in the investment accounts is that in fact they it isn't a conventional grid control that they can just turn on this feature.  It is a bunch of independent controls that someone slapped together.  So right off the bat these controls have even worse performance, plus problems like the keyboard focus not being in the right place at times.   But it also means that if they want "virtualization" they are going to have to code it from scratch.

    On top of that  it seems that everything is "calculated on the fly".  Which is similar to what the balance column does in a non investment account.

    The balance isn't a pre-calculated value, as shown that it changes when you filter out transactions (what is shown is recalculated).  The same is true for the number of shares and such kind of information in the register.  It seems to be calculating all that information every time anything changes. And scrolling is "a change".

    And frankly I think the main reason this hasn't been changed is that they have learned that they don't know how to change it without breaking more things.

    Look at how they have implemented these:
    Move Transactions
    Archive Transactions
    Edit Multiple Transactions

    The first thing you will notice is that they all bring up a new window instead of doing it in the register.  Why?  Because if they do it in a different Window all they have to add the window code and a routine that runs when you select OK to go directly to the database and change things there. This avoids touching any of the code that is associated with the investment register.

    BTW to this day they don't call it a "register", they call it a "list".  That is on purpose.  Even though they look similar and have similar functions the investment "list" is far different under the skin than the non investment registers.
    Signature:
    This is my website: http://www.quicknperlwiz.com/
  • markus1957
    markus1957 SuperUser, Windows Beta Beta
    @Chris_QPW The spending accounts don't seem to rely on GUI at all. Scrolling appears to open a query set that scrolls independently of the register and upon stopping executes a Go to Record command associated with the stop scrolling date.  As different as the table relationships may be, I don't think a scroll control similar to the spending accounts is precluded by the investment account register configuration.

    They have managed to create reports (both spending and investment transactions) that query the tables with a sort of Go to Record built in to those.  I'm not saying it's easy managing the old code but it seems the basic tools to do already exist. A query set with Date and Record Index fields like appears in the spending registers should work in the investment registers.
  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    edited March 2021
    @markus1957 you are right!  I forgot that the non investment accounts don't actually "scroll"!  And yes that certainly is something they can do, but in reality I think that even though the thread say "register difficult to scroll", isn't the "real problem" as in I'm sure entering transactions and such are just as terrible.
    Signature:
    This is my website: http://www.quicknperlwiz.com/
  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    P.S. Just to clarify, when you use the scrollbar button and click and drag the non investment accounts don't scroll the transactions, instead they put up a "tool hint" that shows the date you are on until you release the scrollbar button.  So you can go pick a date and release and jump to there.

    The main reason I never remember that is because I always scroll by using my mouse wheel.  And that does scroll the transactions.
    Signature:
    This is my website: http://www.quicknperlwiz.com/
  • PER
    PER Member ✭✭
    A solution has been found, I think. I started a whole new Quicken File and when I added the brokerage company it pulled in the four accounts, but I did not enable transaction downloading, just overall portfolio balance. So there is no register for those accounts that I can see, however they do accurately download pricing for securities daily and reflect an accurate current balance. My old Quicken data file was about 40Mbytes, and this new file appears to be about 12Mbytes. It was important to me to have those accounts listed as they represent considerable net worth, and monthly each have a fixed transfer of of dividend income which is my actual income to live on.

    It's taken perhaps 12 hours of work over a couple of days to get all the monthly recurring bills that get paid and transfers setup. What's really important to me is seeing my primary checking account projected balance. And that is now recreated correctly.

    The only odd thing that occurred was on one of the four investment accounts the Value over Time graph for the last 12 months is wrong. Current value is okay, but the values of months last year are wrong. Unless someone knows how to troubleshoot this I can live with it as it would hopefully continue to show a correct monthly balance going forward.
  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    It sounds like you found the new option for “Simple Investing” instead of “Complete”.

    The idea around the simple investing mode is that there are people that are only concerned about the current amount of shares/cash in an account and not the full cost basis and such.  So what is being done is instead of downloading all the transactions while simple investing mode is on, it simply put in an “add/subtract shares” and “add/subtract cash” kinds of transactions.  This of course also means that from the point you start doing this there won’t be any history.  And the other thing it means is that instead of the register you see a “dashboard” of the securities/cash.
    Signature:
    This is my website: http://www.quicknperlwiz.com/
  • PER
    PER Member ✭✭
    Chris, yes, it actually appears to be quite useful for me. Frankly its really a brokerage statement and data at the brokerage run on GainsKeeper which they use, and then it goes to the 1099 which I later import into TurboTax. Quicken's simple investing works better for someone with more mature accounts and needs to track overall net worth vs. every transaction. Quicken's record is secondary to the broker 1099.

    I still really like Quicken for the checking account register because frankly independent of whatever happens in four broker accounts it is the interaction between credit card spending, paying off fully each month those bills, and the checking account having sufficient funds coming in to retain appropriate liquidity. The checking account is where the rubber meets the road, so having a good picture on that key account is very helpful.

    I think I have a solution to remain a content Quicken user.
  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    Well now that I know that is what you found/want I thought of a solution to your question about the value over time report.

    You can turn this mode on in your old data file by first making sure this option is on: Edit -> Preferences -> Investment transactions -> Enable Simple Investing (positions-only).  And then going to the register and selecting the gear icon and then Account Details.  In there you will find that you can change the Tracking Method to "Simple - Positions Only".

    This will switch over and from now on only record the positions/cash transactions, but it won't delete the old "complete" transactions, so you past history will be there.  That will make the value over time report be correct.
    Signature:
    This is my website: http://www.quicknperlwiz.com/
  • PER
    PER Member ✭✭
    Chris, thanks, switched a copy of the old file to simple investing and that did work. The file size is still 38Mb and I notice it is slower in closing the file than the file that I rebuilt last week from scratch using the simple investing choice (that is a 12Mb file). If I continue to use the old file, will I eventually hit a limit on the Qdata file size? Also for the new file built last week, only one of the four broker accounts had a flawed value over time chart for the past year. Anyway to fix that, or simply delete it and try to import it again?
  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    There isn't a "data file size limit".  My current data file is 142MB right now and I don't have any problems with it.  And others have even larger files. Quicken's main file is a database.  Databases in general don't read "everything" when requested they seek to the data they need.  Performance problems are more about "what is in this account" especially with investment accounts.  Maybe sometimes like "how many categories do you have", when people have thousands of them.

    There are some limits as documented here:
    https://www.quicken.com/support/size-or-capacity-limitations-quicken-data-file

    But in general people complain much more about performance long before they get even close to those limits.

    As for getting the history correct in the new data file.  You could switch temporarily to Complete mode and entering your own add/remove kind of transactions, but that would be a lot of work.  You could also export a QIF file and import it, but that would most likely bring you basically back to where you are in the original data file.
    Signature:
    This is my website: http://www.quicknperlwiz.com/
This discussion has been closed.