How to change Investment Action based on Memo value?

mikek753b

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

Chris_QPW

Sorry, there isn't any such function in Quicken.

NotACPA

@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!

mikek753b

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

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.

mikek753b

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

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

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.

NotACPA

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?

Chris_QPW

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.

Chris_QPW

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.

mikek753b

> @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

> @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

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>

NotACPA

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.

mikek753b

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

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

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

Chris_QPW

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.

Jim_Harman

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?

Chris_QPW

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.

mikek753b

> @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

how do I embed pictures? instead of shown as attached, that it's shown in the comment

mikek753b

> @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

mikek753b

normal Buy / BoughtX transaction
it has in Memo "Transfer" and BUYTYPE>BUY

mikek753b

this is my manually modified BoughtX to correct ReinvDiv

mikek753b

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

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.

Chris_QPW

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.

mikek753b

> @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

> @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