How do I import Investment data without effecting the cash balance

Options
Strike
Strike Member ✭✭

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

Best Answer

  • Strike
    Strike Member ✭✭
    Answer ✓
    Options

    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

Answers

  • Quicken Kristina
    Quicken Kristina Moderator mod
    Options

    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!

    Quicken Kristina

    Make sure to sign up for the email digest to see a round up of your top posts.

  • Strike
    Strike Member ✭✭
    edited July 8
    Options

    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
    Frankx SuperUser ✭✭✭✭✭
    Options

    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

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

  • NotACPA
    NotACPA SuperUser ✭✭✭✭✭
    Options

    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.

    Q user since February, 1990. DOS Version 4
    Now running Quicken Windows Subscription, Business & Personal
    Retired "Certified Information Systems Auditor" & Bank Audit VP

  • Strike
    Strike Member ✭✭
    edited July 8
    Options

    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
    mshiggins SuperUser ✭✭✭✭✭
    Options

    "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

    Quicken user since Q1999. Currently using QW2017.
    Questions? Check out the Quicken Windows FAQ list

  • Strike
    Strike Member ✭✭
    edited July 11
    Options

    @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
    mshiggins SuperUser ✭✭✭✭✭
    Options

    "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

    Quicken user since Q1999. Currently using QW2017.
    Questions? Check out the Quicken Windows FAQ list

  • Strike
    Strike Member ✭✭
    Options

    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
    Strike Member ✭✭
    edited July 14
    Options

    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
    mshiggins SuperUser ✭✭✭✭✭
    Options

    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

    Quicken user since Q1999. Currently using QW2017.
    Questions? Check out the Quicken Windows FAQ list

  • Strike
    Strike Member ✭✭
    Options

    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
    Strike Member ✭✭
    Answer ✓
    Options

    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