Reconciling credit card statements:
Storner
Quicken Windows Subscription Member
I am taking steps to sharpen my Quicken user skills to take advantage of the power of Quicken. For instance, I need to clean up the opening and closing balances of the last 3 calendar years plus the current year of the of my credit card accounts, and maybe apply the same steps to my bank account and cash accounts). These are all personal accounts, not business accounts or accounts for any third party.
As I go backwards in time with my card accounts, I have discovered sloppy reconciliation on my part for the cards’ account register when compared to the card’s monthly or year-end statement balances. In my Quicken data file, I have multiple credit card account transactions for my various cards. Most of the current card accounts date back multiple years – often 3 to 5 years. The out of use cards go back to 2009.
When first creating the accounts for those cards I was not mindful of assuring an accurate opening balance nor paying much attention to the reconciling the register’s balance with the statement’s balance.
Yes, all the transactions in each month or year are present in the register and match my physical copy of the purchase receipts issued by vendor. But, I’m embarrassed to say, the accuracy of the year-end balance does not match the statement, sometimes showing a year-end negative balance forward of as much as $2,000 to $4,000, creating a larger than preferred liability on my net worth statement..
In my financial affairs, I’m in the fortunate position of not having to depend on absolute balance accuracy to assure my financial well-being… I can be content, so long as all the transactions are recorded and match the card statement and all my bills are paid and my checking account faithfully balances each week.
But, now the balance errors make me uncomfortable, particularly since I want to advance my skills into such areas as asking Quicken to prepare a net worth statement,.
To resolve this, I know the super-users among you will correctly advise that I must bite the bullet and go through the tedious steps of reconciling each of my credit card accounts
The tedium comes about by the volume of yearly transactions – they go back to 2009 in my data file -- not the process of reconciliation. If my cards only dated back a year or two, I could wade through the tedium -- but back to 2009, that’s a bit much…without getting answers from you to these questions below.
I want to draw a “line in the sand” and declare any account or register ending in December 31, 2016 as “off my books.” In other words, I no longer care about the exact accuracy of them. But for 2017 onward, I want accuracy.
How do I create that “line?” Is it by archiving those older files and create a stand-alone data file for each year or multiple years of pre-2017 activity, i.e., January 2009 through December 2016?
If so, it seems I will then more effectively (by my measurement standard) spend my time in recreating the correct opening balance in each card from 2017 onward.
Can I then copy and separate the current cards by year (’17, ’18, 19, and ’20), then resolve each year’s opening and closing balances without, necessarily, affecting the respective balances in next year’s stand alone, and so on until September ’20?
Should I start the process with the current year (2020) and work backwards? Or start from the oldest year (2017) and work forward? Or does it make no difference?
Can I then merge those separate years into a contiguous card account (and register) and insert it back into the overall data file?
If so, I suspect it would be wise to create a test file/s of all this so as not to endanger the integrity of the original data file, however sloppy the balances of the cards within it may be. If it works, can I then merge it into the existing data file?
What are the consequences of all this that would govern the steps I take, particularly those steps that are not reversible?
As I go backwards in time with my card accounts, I have discovered sloppy reconciliation on my part for the cards’ account register when compared to the card’s monthly or year-end statement balances. In my Quicken data file, I have multiple credit card account transactions for my various cards. Most of the current card accounts date back multiple years – often 3 to 5 years. The out of use cards go back to 2009.
When first creating the accounts for those cards I was not mindful of assuring an accurate opening balance nor paying much attention to the reconciling the register’s balance with the statement’s balance.
Yes, all the transactions in each month or year are present in the register and match my physical copy of the purchase receipts issued by vendor. But, I’m embarrassed to say, the accuracy of the year-end balance does not match the statement, sometimes showing a year-end negative balance forward of as much as $2,000 to $4,000, creating a larger than preferred liability on my net worth statement..
In my financial affairs, I’m in the fortunate position of not having to depend on absolute balance accuracy to assure my financial well-being… I can be content, so long as all the transactions are recorded and match the card statement and all my bills are paid and my checking account faithfully balances each week.
But, now the balance errors make me uncomfortable, particularly since I want to advance my skills into such areas as asking Quicken to prepare a net worth statement,.
To resolve this, I know the super-users among you will correctly advise that I must bite the bullet and go through the tedious steps of reconciling each of my credit card accounts
The tedium comes about by the volume of yearly transactions – they go back to 2009 in my data file -- not the process of reconciliation. If my cards only dated back a year or two, I could wade through the tedium -- but back to 2009, that’s a bit much…without getting answers from you to these questions below.
I want to draw a “line in the sand” and declare any account or register ending in December 31, 2016 as “off my books.” In other words, I no longer care about the exact accuracy of them. But for 2017 onward, I want accuracy.
How do I create that “line?” Is it by archiving those older files and create a stand-alone data file for each year or multiple years of pre-2017 activity, i.e., January 2009 through December 2016?
If so, it seems I will then more effectively (by my measurement standard) spend my time in recreating the correct opening balance in each card from 2017 onward.
Can I then copy and separate the current cards by year (’17, ’18, 19, and ’20), then resolve each year’s opening and closing balances without, necessarily, affecting the respective balances in next year’s stand alone, and so on until September ’20?
Should I start the process with the current year (2020) and work backwards? Or start from the oldest year (2017) and work forward? Or does it make no difference?
Can I then merge those separate years into a contiguous card account (and register) and insert it back into the overall data file?
If so, I suspect it would be wise to create a test file/s of all this so as not to endanger the integrity of the original data file, however sloppy the balances of the cards within it may be. If it works, can I then merge it into the existing data file?
What are the consequences of all this that would govern the steps I take, particularly those steps that are not reversible?
0
Answers
-
To draw a line in the sand, I suggest you mark all prior transactions as Reconciled and establish the appropriate reconciled balance for the chosen date in the register. You may then proceed to reconcile the registers forward from that date against statements obtained from the financial institutions for the accounts. Before making any significant changes to a Quicken file, always save a backup: press Ctrl + B1
-
Sherlock, thanks for your input.
Should I first make a separate copy of the Quicken data file that contains my credit cards (and all my other accounts and reports, etc.), and then initiate the tasks you suggested? Am I correct in assuming that if I make a mistake or the steps don’t work out, I’ll still have my main file intact?
Since I only want to reconcile and clean up 1/1/17 forward, should that be the starting point of my separate copy? This would leave all previous dates intact but “off the books” as I mentioned in my initial post. May I assume you don’t recommend or find necessary my initial post where I suggested I should archive “…those older files and create a stand-alone data file for each year or multiple years of pre-2017 activity, i.e., January 2009 through December 2016?”
While I’m at this, would it be a good practice to archive my existing (and currently “only” data file)? May I assume I can return to my archive, if necessary, and reactivate it, so to speak, if my 1/1/17 forward steps lead me into trouble.
You state I should use the “statements obtained from the financial institutions for the accounts.” May I take that to mean I can also use your recommended process for statements other than my credit cards, like my bank/checking account and my cash accounts, thus using the same technique to resolve other balance issues I have in other accounts unrelated to my credit cards?
Are there any cascading consequences to other data in my file by taking these steps?
You also state that I “…may then proceed to reconcile the registers forward.” In other words, I start the reconciliation process on 1/1/17 and NOT on a 1/1/18 or 1/1/19 or 1/1/20 date? Just curious, why is reconciling backwards not a good idea?
Assuming all goes well with the process you recommend, what other areas of my data file should I look at to see if indeed there has been some unexpected cascading effect?
If all checks out accordingly, should this data file now become my on-going file of choice until I archive it at some point in the future?
Thank you Sherlock! Your advice is very helpful.0 -
I suggested you save a backups which you may restore if you make a mistake or the steps don't work out.Storner said:Sherlock, thanks for your input.
Should I first make a separate copy of the Quicken data file that contains my credit cards (and all my other accounts and reports, etc.), and then initiate the tasks you suggested? Am I correct in assuming that if I make a mistake or the steps don’t work out, I’ll still have my main file intact?
It is not necessary to remove earlier transactions.
Since I only want to reconcile and clean up 1/1/17 forward, should that be the starting point of my separate copy? This would leave all previous dates intact but “off the books” as I mentioned in my initial post. May I assume you don’t recommend or find necessary my initial post where I suggested I should archive “…those older files and create a stand-alone data file for each year or multiple years of pre-2017 activity, i.e., January 2009 through December 2016?”
It is good practice to save Quicken file backups.
While I’m at this, would it be a good practice to archive my existing (and currently “only” data file)? May I assume I can return to my archive, if necessary, and reactivate it, so to speak, if my 1/1/17 forward steps lead me into trouble.
Yes.
You state I should use the “statements obtained from the financial institutions for the accounts.” May I take that to mean I can also use your recommended process for statements other than my credit cards, like my bank/checking account and my cash accounts, thus using the same technique to resolve other balance issues I have in other accounts unrelated to my credit cards?
No.
Are there any cascading consequences to other data in my file by taking these steps?
When we reconcile, we have a beginning reconciled balance, an ending balance, and ending date. If you're ignoring the transactions prior to 1/1/17, it is the appropriate 1/1/17 reconciled balance you will need to establish. You can certainly work backwards if you like but it's more work.
You also state that I “…may then proceed to reconcile the registers forward.” In other words, I start the reconciliation process on 1/1/17 and NOT on a 1/1/18 or 1/1/19 or 1/1/20 date? Just curious, why is reconciling backwards not a good idea?
There shouldn't be any unexpected cascading effect.
Assuming all goes well with the process you recommend, what other areas of my data file should I look at to see if indeed there has been some unexpected cascading effect?
Yes.
If all checks out accordingly, should this data file now become my on-going file of choice until I archive it at some point in the future?
Thank you Sherlock! Your advice is very helpful.
1 -
Sherlock, I’m seeking a bit more clarity if you have the patience..
To my question about archiving all my data from 2009 through December 2016, you stated…“It is not necessary to remove earlier transactions” by which I infer you are saying it’s not necessary to archive 2009-2016. While it may not be “necessary,” wouldn't archiving those years keep it pristine and make my then “active” file smaller in size? My current data file is 103mb dating back to 2009. And I do have plans to purchase a new computer this summer. I suspect one might suggest a 103mb file size is De minimis in the grand scheme of things, though isn’t archiving such a file best practice? Thanks for your attention to this.0 -
Saving backups of a Quicken file is archiving. Data segmentation is not archiving and is not pristine. If you use Copy... or Year-End Copy... to remove old transactions from your current Quicken file, the current Quicken file will be smaller but will no longer have access to the transactions you removed. It is not a best practice to remove old transactions from a Quicken file.Storner said:Sherlock, I’m seeking a bit more clarity if you have the patience..
To my question about archiving all my data from 2009 through December 2016, you stated…“It is not necessary to remove earlier transactions” by which I infer you are saying it’s not necessary to archive 2009-2016. While it may not be “necessary,” wouldn't archiving those years keep it pristine and make my then “active” file smaller in size? My current data file is 103mb dating back to 2009. And I do have plans to purchase a new computer this summer. I suspect one might suggest a 103mb file size is De minimis in the grand scheme of things, though isn’t archiving such a file best practice? Thanks for your attention to this.
0
This discussion has been closed.