How to change Investment Action based on Memo value?

one of brokers doesn't export transactions correctly, where ReinvDiv exported as Buy with Memo as Earnings
I need a way to change all Transactions with Memo Earnings from Buy to ReinvDiv

thanks
«1

Answers

  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    Sorry, there isn't any such function in Quicken.
    Signature:
    (I'm always using the latest Quicken Windows Premier subscription version)
    This is my website: http://www.quicknperlwiz.com/
  • NotACPA
    NotACPA SuperUser, Windows Beta Beta
    @mikek753b is there a corresponding DIV action for that BUY??? If not, where does the cash come from for the buy?
    BTW, several brokerages download the Div/Buy combination instead of a simple ReinvDiv, and this is technically correct.
    IF your brokerage only downloads the BUY, you need to complain to them LOUDLY!
    Q user since DOS version 5
    Now running Quicken Windows Subscription, Home & Business
    Retired "Certified Information Systems Auditor" & Bank Audit VP
  • mikek753b
    mikek753b Member ✭✭
    thanks for you replies

    there is no cash, just funds that have qt div distributions, which are reinvested

    yes, I complained to the broker, but nothing happened
    the broker is kind of private for a company, that I can't change
    the name is Newport

    all transactions for this case have to be ReinvDiv , but comes as Buy with Memo as Earning
    so, I'm looking a way to automate it somehow instead make change for each transaction in quicken manually, very time consuming
    while some transactions are real Buy and Memo is empty, that is only way I know which is which

    that's why I asked for Memo column to be able to sort and narrow down what to address

    while Find can provide such list, it doesn't allow to make changes, but only in Investment account, so after the change I need to switch to Find results and so on
    the same for report
    it's like Find and Report are read only for this matter, which is correct

    so, maybe extra as Filter can be added to Investment account to show only filtered subset, but allows complete edit
  • Jim_Harman
    Jim_Harman SuperUser ✭✭✭✭✭
    edited November 26
    I see Newport Group does not support Quicken's Direct Connect.

    Do you get transactions from Newport by downloading a file rather than via One Step Update? If so you might be able to modify the file before importing to Quicken.

    The ImportQIF program available here
    http://www.quicknperlwiz.com/
    might help.
    QWin Premier subscription
  • mikek753b
    mikek753b Member ✭✭
    thank you for helping me
    yes, I manually download from Newport QFX


    Quicken loads it as BoughtX as result that leads to way incorrect data in the account

    so, I'm looking a way to fix this in a batch mode
  • mikek753b
    mikek753b Member ✭✭
    somehow I can't post example from the QFX
    I can preview it, but after I press "Post Comment" nothing was posted
    that QFX fragment has angel brackets , maybe those is the issue to post?
  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    mikek753b said:
    thank you for helping me
    yes, I manually download from Newport QFX


    Quicken loads it as BoughtX as result that leads to way incorrect data in the account

    so, I'm looking a way to fix this in a batch mode
    In that case only the financial institution can fix it.

    I know that with Merril Lynch and my wife's 401K account which is only available using We Connect/QFX that they send reinvestments as buys, so I have to change them each month when they come in.
    Signature:
    (I'm always using the latest Quicken Windows Premier subscription version)
    This is my website: http://www.quicknperlwiz.com/
  • NotACPA
    NotACPA SuperUser, Windows Beta Beta
    A BoughtX transaction requires an account name to be in the category field, with that account name surrounded by Square Brackets, e.g., [Checking Account}.
    So, what's in the category field of those BoughtX transactions which should be ReinvDiv?  And, do you have other BoughtX transactions in the account?
    Q user since DOS version 5
    Now running Quicken Windows Subscription, Home & Business
    Retired "Certified Information Systems Auditor" & Bank Audit VP
  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    mikek753b said:
    somehow I can't post example from the QFX
    I can preview it, but after I press "Post Comment" nothing was posted
    that QFX fragment has angel brackets , maybe those is the issue to post?
    It might show up later, sometimes comments are sent to the moderators for review to prevent spamming.
    Signature:
    (I'm always using the latest Quicken Windows Premier subscription version)
    This is my website: http://www.quicknperlwiz.com/
  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    mikek753b said:
    so, I'm looking a way to fix this in a batch mode
    Quicken doesn't have any way to change the action type, except editing the individual transactions.
    Signature:
    (I'm always using the latest Quicken Windows Premier subscription version)
    This is my website: http://www.quicknperlwiz.com/
  • mikek753b
    mikek753b Member ✭✭
    > @NotACPA said:
    > A BoughtX transaction requires an account name to be in the category field, with that account name surrounded by Square Brackets, e.g., [Checking Account}.So, what's in the category field of those BoughtX transactions which should be ReinvDiv?  And, do you have other BoughtX transactions in the account?

    yes, the QFX has the account and securities set correctly, this is about incorrect transactions

    the example fragment from my QFX just isn't going through :-(
    otherwise it should help to see it and understand the issue

    in short

    BUYMF
    INVBUY
    INVTRAN
    ....
    BUYTYPE>BUY
  • mikek753b
    mikek753b Member ✭✭
    > @Chris_QPW said:
    >
    > I know that with Merril Lynch and my wife's 401K account which is only available using We Connect/QFX that they send reinvestments as buys, so I have to change them each month when they come in.

    do you do it as manual edit one transaction at a time?
    lets say 5 investments that produce Divs, so you 5 times do manual edit to change action to ReinvDiv, is it what you do?

    tnx
  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    mikek753b said:
    > @Chris_QPW said:
    >
    > I know that with Merril Lynch and my wife's 401K account which is only available using We Connect/QFX that they send reinvestments as buys, so I have to change them each month when they come in.

    do you do it as manual edit one transaction at a time?
    lets say 5 investments that produce Divs, so you 5 times do manual edit to change action to ReinvDiv, is it what you do?

    tnx

    Yes, I do it as a manual entry.  It used to be two securities a month, change from buy to reinvest dividend, but now it is one since we exchanged out of one of the securities.  Doesn't tend to be much of a problem, hit edit, change from buy to reinvest dividend and the rest of the information is correct.

    mikek753b said:
    in short

    BUYMF
    INVBUY
    INVTRAN
    ....
    BUYTYPE>BUY
    A proper OFX entry for reinvestment of dividends follow this format:


    Example:
                        <REINVEST>
                            <INVTRAN>
                                <FITID>488256805
                                <DTTRADE>20110407160000.000[-5:EST]
                                <DTSETTLE>20110407160000.000[-5:EST]
                                <MEMO>DIVIDEND REINVESTMENTDIVIDEND REINVESTMENT
                            </INVTRAN>
                            <SECID>
                                <UNIQUEID>464288687
                                <UNIQUEIDTYPE>CUSIP
                            </SECID>
                            <INCOMETYPE>DIV
                            <TOTAL>-109.25
                            <SUBACCTSEC>CASH
                            <UNITS>2.753
                            <UNITPRICE>39.6839811
                        </REINVEST>

    Signature:
    (I'm always using the latest Quicken Windows Premier subscription version)
    This is my website: http://www.quicknperlwiz.com/
  • NotACPA
    NotACPA SuperUser, Windows Beta Beta
    I'm not interested in what shows in the QFX, I'm asking what shows in the CATEGORY field of these incorrect BoughtX transactions IN QUICKEN ... and are there other, correct, BoughtX transactions in the investment account.
    Q user since DOS version 5
    Now running Quicken Windows Subscription, Home & Business
    Retired "Certified Information Systems Auditor" & Bank Audit VP
  • mikek753b
    mikek753b Member ✭✭
    yes, I download QFX from Newport manually

    no, I was not able to figure out how ImportQIF can help for my use case , looks like it's just for CSV for "as is" plus adds missing account info

    this is what in "my" QFX as example

    <BUYMF>
    <INVBUY>
    <INVTRAN>
    <FITID>20200131015627261831386
    <DTTRADE>20200131120000
    <MEMO>Earnings
    </INVTRAN>
    <SECID>
    <UNIQUEID>78462F103
    <UNIQUEIDTYPE>CUSIP
    </SECID>
    <UNITS>0.561123
    <UNITPRICE>321.73
    <TOTAL>-180.53
    <SUBACCTSEC>OTHER
    <SUBACCTFUND>OTHER
    </INVBUY>
    <BUYTYPE>BUY
    </BUYMF>

    as you can see it has "BUY" that instructs Quicken for BoughtX action, while ReinvDiv is really needed
    as result I have way incorrect data in this quicken account

    thank you all for helping me
  • mikek753b
    mikek753b Member ✭✭
    There was an error rendering this rich post.> @NotACPA said:
    > I'm not interested in what shows in the QFX, I'm asking what shows in the CATEGORY field of these incorrect BoughtX transactions IN QUICKEN ... and are there other, correct, BoughtX transactions in the investment account.

    sorry, I don't see any CATEGORY field for those transactions in the quicken Investment account
    the cash is from the same account, tracked in -Cash separate account

    where should I go to see CATEGORY field?

    I see only Action, Security and related investment fields per Action type, sure date and description and Memo

    what I'm missing?
  • NotACPA
    NotACPA SuperUser, Windows Beta Beta
    If you click on any of these BoughtX transactions, and then click the EDIT button that's on the same line, a "Transfer Account" field will be in the Edit Dialog.
    OR, if you just click on the transaction, the "Transfer Account" (aka, the category) will be in the Description field below the internal transaction name
    Q user since DOS version 5
    Now running Quicken Windows Subscription, Home & Business
    Retired "Certified Information Systems Auditor" & Bank Audit VP
  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    I believe that @mikek753b is using "Show cash in a checking account", correct?

    Buys will be changed into BoughtX when this feature is being used since the cash for the transaction comes from the cash account.
    Signature:
    (I'm always using the latest Quicken Windows Premier subscription version)
    This is my website: http://www.quicknperlwiz.com/
  • Jim_Harman
    Jim_Harman SuperUser ✭✭✭✭✭
    Or it might be a Single Mutual Fund (SMF) account, which cannot hold cash.

    @mikek753b, if you go to the account, click on the gear, and pick Edit account details, is there a Show cash in a checking account option set to Yes? Is there a Single Mutual Fund option? Is it set to Yes?
    QWin Premier subscription
  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    edited November 26
    BTW Since I saw that these were set to OTHER, and mine was CASH, I tested if this made any difference:
    <SUBACCTSEC>OTHER
    <SUBACCTFUND>OTHER

    It didn't.  Which makes perfect sense.  There isn't any information in the data sent that would allow Quicken to know what account to connect to a BoughtX if it isn't either "show in cash account" or Single Mutual Fund.
    Signature:
    (I'm always using the latest Quicken Windows Premier subscription version)
    This is my website: http://www.quicknperlwiz.com/
  • mikek753b
    mikek753b Member ✭✭
    edited November 27
    > @Jim_Harman said:
    > Or it might be a Single Mutual Fund (SMF) account, which cannot hold cash.
    >
    > @mikek753b, if you go to the account, click on the gear, and pick Edit account details, is there a Show cash in a checking account option set to Yes? Is there a Single Mutual Fund option? Is it set to Yes?

    yes, for separate cash account
    it's multi funds / stocks account to track each with details
  • mikek753b
    mikek753b Member ✭✭
    how do I embed pictures? instead of shown as attached, that it's shown in the comment
  • mikek753b
    mikek753b Member ✭✭
    > @NotACPA said:
    > If you click on any of these BoughtX transactions, and then click the EDIT button that's on the same line, a "Transfer Account" field will be in the Edit Dialog.OR, if you just click on the transaction, the "Transfer Account" (aka, the category) will be in the Description field below the internal transaction name

    please, see for attached pictures
    let me know if I can provide more info
    3.JPG 49.1K
    4.JPG 90.9K
    5.JPG 74.5K
  • mikek753b
    mikek753b Member ✭✭
    normal Buy / BoughtX transaction
    it has in Memo "Transfer" and BUYTYPE>BUY
    6.JPG 44.4K
    7.JPG 95.5K
  • mikek753b
    mikek753b Member ✭✭
    this is my manually modified BoughtX to correct ReinvDiv
    9.JPG 107.3K
  • mikek753b
    mikek753b Member ✭✭
    thank you all for all your help
    I need to modify above 100 transactions
    so far I wasn't able to find any batch / bulk / replacement or etc that can help me with this situation

    yes, Quicken got batch for Reconcile and Delete for Investment account, while I need extra
  • NotACPA
    NotACPA SuperUser, Windows Beta Beta
    edited November 27
    SO, If I'm understanding the situation correctly, Newport-Cash should be showing a significant negative balance ... because you're showing the cash for all of these BoughtX transaction as coming from Newport-Cash while there is no INFLOW into that account.  Correct?

    SO, if you can figure out for each security in Newport what the correct SUM of all dividends received is, simply manually input a DIVX transaction into Newport with the cash shown as going to Newport-Cash.
    THUS, Newport-Cash will eventually return to  $0, and you'll have Dividends recorded for each of your securities ... and a combo of DIV/BUY equals a ReinvDiv.

    If you don't want to go back indefinitely in time, simply input the Div transactions for THIS year only ... and then input into Newport-Cash a transaction to zero out that account using [Newport-Cash] itself as the category.  This will reduce the value to $0 without impacting any category.
    Q user since DOS version 5
    Now running Quicken Windows Subscription, Home & Business
    Retired "Certified Information Systems Auditor" & Bank Audit VP
  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    mikek753b said:
    how do I embed pictures? instead of shown as attached, that it's shown in the comment
    Until you get more points in this forum you can't embed images, only attach them.

    On the subject of batch changing transactions, I can certainly see why they haven't put that in.
    In some cases, you can change you might be able to just change and action and the rest of the information would be correct, but not all the time.  And if any of the other details needed to change each transaction would need different entries.
    Signature:
    (I'm always using the latest Quicken Windows Premier subscription version)
    This is my website: http://www.quicknperlwiz.com/
  • mikek753b
    mikek753b Member ✭✭
    > @Chris_QPW said:
    > Until you get more points in this forum you can't embed images, only attach them.
    >
    > On the subject of batch changing transactions, I can certainly see why they haven't put that in.
    > In some cases, you can change you might be able to just change and action and the rest of the information would be correct, but not all the time.  And if any of the other details needed to change each transaction would need different entries.

    yes, Action replacement only will work for a few, but for sure isn't for every Action type agreed
    while in my case BoughtX to ReinvDiv is kind of straightforward, sure Action fields are a bit diff, but maps 1:1 - IMHO

    yes, it will be huge logic behind to make it generic, if ever possible

    thanks
  • mikek753b
    mikek753b Member ✭✭
    > @NotACPA said:
    > SO, If I'm understanding the situation correctly, Newport-Cash should be showing a significant negative balance ... because you're showing the cash for all of these BoughtX transaction as coming from Newport-Cash while there is no INFLOW into that account.  Correct?
    > SO, if you can figure out for each security in Newport what the correct SUM of all dividends received is, simply manually input a DIVX transaction into Newport with the cash shown as going to Newport-Cash.THUS, Newport-Cash will eventually return to  $0, and you'll have Dividends recorded for each of your securities ... and a combo of DIV/BUY equals a ReinvDiv.
    > If you don't want to go back indefinitely in time, simply input the Div transactions for THIS year only ... and then input into Newport-Cash a transaction to zero out that account using [Newport-Cash] itself as the category.  This will reduce the value to $0 without impacting any category.

    thank you for helping me

    yes, I like your idea about "placeholder" to add 1 per security DIV adjustment

    in my case I have a mess in this account due to distributions, which also incorrectly adds cash to the account
    so, I try to address it in steps:
    1. change all BoughtX with Memo Earnings to ReinvDiv
    2. figure out how to convert Distributions to Paystubs - this will be my another dedicated topic later. I think only Split with negative values is currently possible for such deferral distributions

    currently the account shows totally absurd cash position and security positions
    I've moved slowly Qt by Qt to match the statements, very slow process for me, to get this account to good state