How do I import Investment data without effecting the cash balance

Strike

Hello,

I am trying to import my Fidelity 401K Historical translations through the QIF file that I downloaded from them, but when I upload it to Quicken, it impacts the cash balance of the investment account. I am not sure how the process does not impact my cash balance in the regular transactions that are loaded through the automated update, but I need to be able to do the same thing as it never shows the cash going in. The investments are made automatically right as they get the money from my employer.

Any help in getting these historical transactions loaded without effecting the cash balance wound be great.

Thank you

Trevor

Strike

I think I have it all figured out. I need to also change the U to a $ and instead of deleting the I, I need to get it so that it is the correct price per share. So my updated line looks like this:

!Type:Invst
D06/30/2021
NBuyx
YFID 500 INDEX
I149.52462
Q0.589
$88.07
T88.07
L[Trevor's Huntington 401K]
MContribution
^

I also found that if you add CC into the lines, that also marks the translation as cleared and CX marks it as Reconciled.

@mshiggins thank you for your help and guidance.

Thank you

Quicken Kristina

Hello @Strike,

To assist with this, please provide more information. Are you importing a .QIF file or a .QFX file into Quicken? Are you importing it directly into the existing account, or are you adding it as a new account, then moving the transactions to the existing account?

Does the account have placeholder transactions? Quicken typically creates those to make up for missing history when you connect the account. It is possible that what is throwing off your cash balance is you're adding in history that the placeholder transactions were there to offset.

I look forward to your response!

Strike

Hello @Quicken Kristina,

I am uploading a QIF file to an existing account in which I had removed the place holders to.

As you can see from the above, the automated load from Fidelity does not effect the cash balance, and I want to do the same.

Thank you

Trevor

Frankx

Hi @Strike

The Buy and Sell transactions shown in your account above are being entered using the BoughtX and SoldX actions. Those are different from the Bought and Sold actions because the cash for the "…X" transactions either goes into or comes from another Quicken account, whereas the cash for regular Bought or Sold transactions comes from, or goes into the same account.

Also I think it is likely that those entries are "one sided", in that they probably not actually being posted to any other account in your Quicken file. Which, for most users would be problematic.

Frankx

NotACPA

A "one-sided" transaction, such as @Frankx references, is one in which the Category for the transfer is that originating account itself.

So, if your account is named "Brokerage" the category would be [Brokerage], note the square brackets which is what makes it a transfer.

Strike

Hi @Frankx or @Quicken Kristina,

The X at the end of those is how it comes in directly from Fidelity. I am assuming that this is normal for 401K and 403B accounts as the cash is never placed into the account, it is pulled from the master employer account that was funded for all the employees.

Here is a few of the transactions from the QIF file I downloaded from Fidelity. Is there something I need to change to make way it look if it was coming directly from Fidelity?

!Type:Invst
D06/30/2021
NBuy
YFID 500 INDEX
I149.59000
Q0.589
U88.07
T88.07
MContribution^
D06/30/2021
NBuy
YFID EXTD MKT IDX
I90.76000
Q0.970
U88.09
T88.09
MContribution^
D06/30/2021
NBuy
YFID TOTAL INTL IDX
I14.88000
Q5.919
U88.08
T88.08
MContribution^
D06/30/2021
NBuy
YVANGUARD TARGET 2040
I50.12000
Q1.757
U88.08
T88.08
MContribution^

Thank you for your help

mshiggins

"Is there something I need to change to make way it look if it was coming directly from Fidelity?"

Yes, but I doubt you will want to expend the time to do it for multiple downloads.

You would have to modify multiple records in the QIF file; then add 1 record for every QIF file "transaction" that would affect cash.

The QIF file records that begin with the letter "N" contain the Quicken investment account "Action" value.

In the QIF file you would need to change (using Notepad or similar) all: 
- NBuy to NBuyX
- NSell to NSellX
- NDiv to NDivX
- And any other Action values that would affect cash would need to have an "X" suffixed to their existing Action value.

Also for each QIF file transaction with an Action code you changed to have a X suffix, you would have to add an  "L" record, specifying the Quicken account where the cash was transferred TO or FROM.

So, for example, your QIF file transaction, 

D06/30/2021
NBuy
YFID TOTAL INTL IDX
I14.88000
Q5.919
U88.08
T88.08
MContribution^

would become (assuming the name of your Quicken 401k account is "My401k"),

D06/30/2021
NBuyX
YFID TOTAL INTL IDX
I14.88000
Q5.919
U88.08
T88.08
L[My401k]
MContribution^


The following information "might" be useful to those trying to help:
- the "Account type" on the General tab of the Edit Account Details dialog for your 401k account?
- the Quicken name of your 401k account?
- the account name that appears in the "Use cash for this transaction From/To" dropdown for your xxxX transactions?
- what does Fidelity do in their system with the cash from your SellX transactions?
- why are you downloading a QIF file when your Direct Downloads from Fidelity were working as you wanted them to?

-JP

Strike

@mshiggins Thank you for your help. I will take a look at this and give it a try.

Answers to your questions:
- the "Account type" on the General tab of the Edit Account Details dialog for your 401k account? - 401k or 403(b)
- the Quicken name of your 401k account? - Trevor's Huntington 401K
- the account name that appears in the "Use cash for this transaction From/To" dropdown for your xxxX transactions? - Unknown Source
- what does Fidelity do in their system with the cash from your SellX transactions? - These are the quarterly fees they charge us
- why are you downloading a QIF file when your Direct Downloads from Fidelity were working as you wanted them to? - I am trying to load my history in as I just started with Quicken in January 2024. Also, I have other 401k accounts for my wife that I will have to bring in that I may have the same issue with as they do not connect to Quicken.

Based on the information in the account, would my actions look like this?

D06/30/2021
NBuyX
YFID TOTAL INTL IDX
I14.88000
Q5.919
U88.08
T88.08
L[Unknown Source]
MContribution^

or

D06/30/2021
NBuyX
YFID TOTAL INTL IDX
I14.88000
Q5.919
U88.08
T88.08
L[Trevor's Huntington 401K]
MContribution^

mshiggins

"Based on the information in the account, would my actions look like this?

D06/30/2021
NBuyX
YFID TOTAL INTL IDX
I14.88000
Q5.919
U88.08
T88.08
L[Unknown Source]
MContribution^

or

D06/30/2021
NBuyX
YFID TOTAL INTL IDX
I14.88000
Q5.919
U88.08
T88.08
L[Trevor's Huntington 401K]
MContribution^ "


The second one.
Also I forgot to mention before: the caret (^) must be on a separate "line" (or row). 
So the transaction would look like this:

D06/30/2021
NBuyX
YFID TOTAL INTL IDX
I14.88000
Q5.919
U88.08
T88.08
L[Trevor's Huntington 401K]
MContribution
^

How many QIF file "transactions" do you have?
If you're comfortable using Excel, I think there is a way to make the QIF file modification process easier - especially adding the "L" record to multiple "transactions".

-JP

Strike

I tried loading this test file:
!Type:Invst
D06/30/2021
NBuyX
YFID 500 INDEX
I149.59000
Q0.589
U88.07
T88.07
L[Trevor's Huntington 401K]
MContribution^
D06/30/2021
NBuyX
YFID EXTD MKT IDX
I90.76000
Q0.970
U88.09
T88.09
L[Trevor's Huntington 401K]
MContribution^
D06/30/2021
NBuyX
YFID TOTAL INTL IDX
I14.88000
Q5.919
U88.08
T88.08
L[Trevor's Huntington 401K]
MContribution^
D06/30/2021
NBuyX
YVANGUARD LIFESTRATEGY GROWTH INV
I50.12000
Q1.757
U88.08
T88.08
L[Trevor's Huntington 401K]
MContribution^

and got this:

I have tried both checking and unchecking the Special handling for transfers and get the same results:

Is there something else I am missing?

Thank you

Strike

If I click Edit in the transaction and then click Enter/done, that fixes the issue. However, I don't think I should have to do that for every transaction I import since it will 8 buy transactions for every month, and 4 sell transactions every quarter. Is there something I am missing, or is this a quicken bug that needs to be fixed?

Thank you

mshiggins

As I noted in my previous post:  
"Also I forgot to mention before: the caret (^) must be on a separate "line" (or row)."

Then I posted an example, showing the caret in its own separate row. Here's that example again.

D06/30/2021
NBuyX
YFID TOTAL INTL IDX
I14.88000
Q5.919
U88.08
T88.08
L[Trevor's Huntington 401K]
MContribution
^

In your screenshot: 
Regarding the difference between the resulting Quicken (FID TOTAL INTL IDX) transaction's - "Inv Amt" ($88.07) and the "Cash Amt" ($88.08); I think that's likely caused by submitting the total purchase amount ($88.07), the price/share ($14.88), and the number of shares purchased (5.919).

It is better to submit only 2 of the 3 factors in the formula, and let Quicken compute the third. 

Since the critical factors in the BoughtX transactions are Amount paid and shares purchased, you may get better results by deleting the price/share records in the QIF file. You can delete the QIF file records that begin with the letter I (as in I149.59).

Taking that approach will produce a QIF transaction like this:

D06/30/2021
NBuyX
YFID TOTAL INTL IDX
Q5.919
U88.08
T88.08
L[Trevor's Huntington 401K]
MContribution
^

That tells Quicken you spent $88.08 and purchased 5.919 shares - then Quicken will compute the price of each share purchased as $14.880892/share ($88.08 / 5.919) - in other words; not exactly $14.88/share.


I do hope you have a backup of your original Quicken file, so you can restore it and redo the QIF file import - after repairing the QIF file.

-JP

Strike

Thank you @mshiggins.

I am not sure why when I pasted my transactions in, that the ^ was not on a separate line, but it was on a separate line. I did see there were spaces at the end of the MContribution line. I have deleted those, removed the line that starts with an I, and placed an extra line before the ^. I am still getting the same results except, now there is not investment amount. Once I click edit and enter/done, the translation looks like it should.

Just so you know, the import process will not even take the file if the ^ is on the same line as the MContribution.

Any additional help is appreciated.

Strike

I think I have it all figured out. I need to also change the U to a $ and instead of deleting the I, I need to get it so that it is the correct price per share. So my updated line looks like this:

!Type:Invst
D06/30/2021
NBuyx
YFID 500 INDEX
I149.52462
Q0.589
$88.07
T88.07
L[Trevor's Huntington 401K]
MContribution
^

I also found that if you add CC into the lines, that also marks the translation as cleared and CX marks it as Reconciled.

@mshiggins thank you for your help and guidance.

Thank you