can i extract and create addition columns

for example if i have transactions from my bank as
4/01 DBT CRD 0713 03/31/20 0175444 8.90
292 HARDEES
WATFORD CITY ND C#xxxx

i would like to split it into additional columns

Trans date 03/31/20
city WATFORD CITY
state ND
last 4 digits of card number xxxx

any reference link on how to do this

Best Answer

  • Boatnmaniac
    Boatnmaniac SuperUser ✭✭✭✭✭
    Answer ✓
    @wtcubar - There are some things you can do to get close to what you are looking for that can make it a little less manual but it does require some set up and it will not eliminate the need to do a lot of manual entry and edits of transactions.
    1. Downloaded Transaction Dates: Bank and credit card Financial institutions (FIs) download 2 dates into the account registers....the Transaction Date and the Posting Date.  The default date field column in the register has the column header "Date" and for most banks and many credit cards (especially if they are set up with Direct Connect instead of Express Web Connect) the dates that get entered in this column are the actual Transaction Dates, not the Posting Dates.  For the other bank and credit card FIs it is the Posting Dates.  What you might want to consider doing is to add a column for the Posting Dates to your registers by going to:  Upper right Gear icon > Register Columns > check the box for Downloaded Posting Date.   You will need to do this for each account individually as it is not a global setting.  The benefit of doing this:  You can manually edit the "Date" column transaction date (if/when needed) to show the actual transaction date.  The "Posting Date" column is not editable as that is the date that the FI cleared the transaction.  Then you will have both the actual Transaction Date and the Posting Date (which can be really helpful in cross referencing a transaction to what is shown online and in statements from the financial institution).
    2. Multiple Store Locations:  Many stores, gas stations, restaurants, etc., with multiple locations will have their location included in the downloaded name of the business.  When you get a downloaded transaction from "TSC Williston 2345" (just a hypothetical example) you can set up a Renaming Rule to rename it "TSC WILLISTON ND".  Then every time you get a downloaded transaction from "TSC Williston 2345" it will be entered into your register as "TSC WILLSTON ND".  You can set up separate Renaming Rules for each business location. If you do not want to create Renaming Rules for them you can always manually rename the transaction after it is downloaded or you can manually enter the transaction before downloading and Quicken will match the downloaded transactions to it (usually).  Using either of these processes will cause each business location to be a unique Payee which can then be filtered and sorted in Quicken registers and reports.
    3. Credit Cards linked to a Main Credit Card:  Some FIs will download some of the accounts differentiation (either the last few digits of the card number or the name of the person the card was assigned to) into the transactions Memo field.  But many do not do this.  You might want to play around with Tags and set up a separate Tag for each of the linked credit cards.  You will need to manually tag each transaction, then, but you will not need to type it in each time and this will keep your Memo and Notes fields open to put other information into.  (The Reference column can be used like this, too, but you will always get a warning when you enter a reference number that you've used before and will need to manually approve the duplicate reference entry.  Plus, some FIs, especially banks, will download their own reference number information, such as check numbers, and then you will have to decide which reference number is more important for you to keep in the transaction.  I personally think the Tags option would be a better way to manage this and it will eliminate this Reference number conflict.)
    For what it's worth, I used to try to maintain my registers in a manner similar to what you are wanting but I found that it was taking a lot of time and effort to keep all the transactions properly edited and maintained, especially with regard to the credit cards that are linked to a main credit card and the multiple store locations.  For me, in the end I determined there really wasn't much real benefit from doing it that way so I stopped doing it...and I've really enjoyed the amount of time that decision freed up for me.  Of course, your situation might be different and you really do need all that detail but only you can make that determination.

    (Quicken Classic Premier Subscription: R54.16 on Windows 11)

Answers

  • Frankx
    Frankx SuperUser ✭✭✭✭✭
    edited March 2021
    Hi @wtcubar,

    If I understand correctly, you are asking whether you can "create additional columns" in a Quicken account register. Generally, the registers have columns that are both limited in number and are designated to hold specific data.  As a practical matter, I would question why you feel the need to add columns that, in your example, are parsed so specifically and moreover why such minutia would each need to have a separate column.  Furthermore to the best of my knowledge - you will need to manually enter much of these extra fields, so that is something you need to consider in terms of level of effort.

    That being said - below is a quick example of what I think you want to do, using the data you provided.  It was using all available fields for this type of account (credit card account).


    Hope this helps.

    Frankx

                            Quicken Home, Business & Rental Property - Windows 10-Home Version

                                             - - - - Quicken User since 1984 - - - 
      -  If you find this reply helpful, please click "Helpful" (below), so others will know! Thank you.  -

  • wtcubar
    wtcubar Member ✭✭
    One example
    For mileage records for one, I need the purchase date, no the posted date. Yes I could always look in the memo field a day or two prior to posted date.

    There are multiple locations of the same store I would like have quick access to the info instead of looking in the downloaded memo/memo
    TSC WILLISTON ND
    TSC WATFORD CITY ND
    TSC SIDNEY MT

    I have multiple card numbers tied to a single acct, again it would help for quick finds,analysis
    All this info is imbedded in the transaction but not nicely laid out for quick analysis


    How did you get card xxxx in the reference column
  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    @wtcubar he did it manually. There isn't anything in Quicken that is going to automatically breakup this information and put it in different fields than where it came from, let alone have new columns for this data.
    Signature:
    This is my website: http://www.quicknperlwiz.com/
  • Frankx
    Frankx SuperUser ✭✭✭✭✭
    Hi again @wtcubar

    I gave an example of something that was similar to what you described (albeit in very few words) which was certain specific data items, with each in a separate register column.  Maybe you misunderstood the part where I said "you will need to manually enter much of these extra fields, so that is something you need to consider in terms of level of effort"

    To be honest I was trying to warn you that Quicken is likely not the right tool for you to use to deliver the data you need in the form you want.  While I proved that you "could" show the information you mentioned in a one-line entry, I expect the time and effort you'll expend - trying to create those entries manually - will be significantly more than you'll want to expend.  Rather, I would advise you to look for another application into which you can import the credit card data and then manipulate to produce the reports you need.

    Good luck.

    Frankx


                            Quicken Home, Business & Rental Property - Windows 10-Home Version

                                             - - - - Quicken User since 1984 - - - 
      -  If you find this reply helpful, please click "Helpful" (below), so others will know! Thank you.  -

  • Boatnmaniac
    Boatnmaniac SuperUser ✭✭✭✭✭
    Answer ✓
    @wtcubar - There are some things you can do to get close to what you are looking for that can make it a little less manual but it does require some set up and it will not eliminate the need to do a lot of manual entry and edits of transactions.
    1. Downloaded Transaction Dates: Bank and credit card Financial institutions (FIs) download 2 dates into the account registers....the Transaction Date and the Posting Date.  The default date field column in the register has the column header "Date" and for most banks and many credit cards (especially if they are set up with Direct Connect instead of Express Web Connect) the dates that get entered in this column are the actual Transaction Dates, not the Posting Dates.  For the other bank and credit card FIs it is the Posting Dates.  What you might want to consider doing is to add a column for the Posting Dates to your registers by going to:  Upper right Gear icon > Register Columns > check the box for Downloaded Posting Date.   You will need to do this for each account individually as it is not a global setting.  The benefit of doing this:  You can manually edit the "Date" column transaction date (if/when needed) to show the actual transaction date.  The "Posting Date" column is not editable as that is the date that the FI cleared the transaction.  Then you will have both the actual Transaction Date and the Posting Date (which can be really helpful in cross referencing a transaction to what is shown online and in statements from the financial institution).
    2. Multiple Store Locations:  Many stores, gas stations, restaurants, etc., with multiple locations will have their location included in the downloaded name of the business.  When you get a downloaded transaction from "TSC Williston 2345" (just a hypothetical example) you can set up a Renaming Rule to rename it "TSC WILLISTON ND".  Then every time you get a downloaded transaction from "TSC Williston 2345" it will be entered into your register as "TSC WILLSTON ND".  You can set up separate Renaming Rules for each business location. If you do not want to create Renaming Rules for them you can always manually rename the transaction after it is downloaded or you can manually enter the transaction before downloading and Quicken will match the downloaded transactions to it (usually).  Using either of these processes will cause each business location to be a unique Payee which can then be filtered and sorted in Quicken registers and reports.
    3. Credit Cards linked to a Main Credit Card:  Some FIs will download some of the accounts differentiation (either the last few digits of the card number or the name of the person the card was assigned to) into the transactions Memo field.  But many do not do this.  You might want to play around with Tags and set up a separate Tag for each of the linked credit cards.  You will need to manually tag each transaction, then, but you will not need to type it in each time and this will keep your Memo and Notes fields open to put other information into.  (The Reference column can be used like this, too, but you will always get a warning when you enter a reference number that you've used before and will need to manually approve the duplicate reference entry.  Plus, some FIs, especially banks, will download their own reference number information, such as check numbers, and then you will have to decide which reference number is more important for you to keep in the transaction.  I personally think the Tags option would be a better way to manage this and it will eliminate this Reference number conflict.)
    For what it's worth, I used to try to maintain my registers in a manner similar to what you are wanting but I found that it was taking a lot of time and effort to keep all the transactions properly edited and maintained, especially with regard to the credit cards that are linked to a main credit card and the multiple store locations.  For me, in the end I determined there really wasn't much real benefit from doing it that way so I stopped doing it...and I've really enjoyed the amount of time that decision freed up for me.  Of course, your situation might be different and you really do need all that detail but only you can make that determination.

    (Quicken Classic Premier Subscription: R54.16 on Windows 11)

  • wtcubar
    wtcubar Member ✭✭
    like you said a little work on my side. memo field is the transaction date, in payee field i do lie TSC-WILLISTON, TSC-SIDNEY. For mileage, i created a tag called farmmiles for transactions where i drove to get the item and number the receipt and put it in the check # column. I then created a off-line bank account called mileage. the date field is set to the transaction date, check # is the receipt number, payee is MILEAGE-CITYNAME, memo is the starting mileage of vehilcle-short name of store ( 123456-TSC,WALLMART), catorgy is drive-car or drive-pickup, tag is CITY-MILES, lie WILLISTON-92, WATFORD-50. Then i post in the payment field one cent for each mile. so .92, .50. so if the total in the account is 100.25 would mean i drove 10,025 miles on a vehicle but should be able to do reports for per vehicle or per town. Seems to work, it is like im not driving every day to get part or items for the farm.....figure i would share what i came up with
  • Boatnmaniac
    Boatnmaniac SuperUser ✭✭✭✭✭
    Thanks for sharing @wtcubar .  I'm glad you found my suggestions helpful.  Your response on this can also be helpful to others who are looking to do the same thing.  Thanks, again.

    (Quicken Classic Premier Subscription: R54.16 on Windows 11)

This discussion has been closed.