[GNC] QIF imports for security pricing

Tue Sep 7 17:23:29 EDT 2021

Oh! Sorry for not understanding it, David.  My bad here. Appreciate that working framework and the sample your provided does work for publicly traded security as expected. 

By way of background … The reason is because the securities of concern are mutual funds and they are NOT publicly available ones; they only can be purchased in the company’s 401K account (effectively a private security issue)! I can download transaction in CSV format ONLY from the portal of the plan administrator but there is no way to pull their prices from likes of Yahoo Finance or otherwise any other source. For that fact, they don’t even have typical mutual fund symbols to start with. With only option to download transactions in CSV file format, I convert that CSV into QIF format programmatically to import them while injecting pricing of each transaction in the conversion process (hoping) to import that as well.

Kalpesh…

From: D. <sunfish62 at yahoo.com> 
Sent: Tuesday, September 07, 2021 3:57 PM
To: Kalpesh Patel <kalpesh.patel at usa.net>
Cc: David Carlson <david.carlson.417 at gmail.com>; David Reiser via gnucash-user <gnucash-user at gnucash.org>
Subject: RE: [GNC] QIF imports for security pricing

I don't understand. Why would you need to KEEP importing prices? Once you've imported your transactions, you can simply update the price db separately using the process I described.

I attach a Google sheet that gets prices for me. Run the Advanced Portfolio report for your books, select all, copy, put your cursor in cell A2 of the SRC sheet, and paste. Then go to the PriceDB tab. Select File->Download->Comma-separated values and save the data. Then in GnuCash, choose File->Import->Import Prices from a CSV file. Import the file you just created, using the information in the data window to guide you. I just tested this, and it works. 

David T.

  _____  

From: Kalpesh Patel 
Sent: Tue Sep 07 15:02:22 EDT 2021
To: "'D.'" , 'David Carlson' 
Cc: 'David Reiser via gnucash-user' 
Subject: RE: [GNC] QIF imports for security pricing

David & David – that was my next route. 

I can write a small program (using python or shell) to convert QIF prices into CSV format but I am tripped up with the "From Namespace" column details as I cannot move to next screen in the import process as it wants correct column identified for import. I did try that with just three columns file - date, security name and price – but no luck. Not sure what it is expecting in that column (is it the issuer like NYSE, NASDAQ, FUND?) but haven’t been able to figure it out so far. Anywhere I can find that info is welcome. It sounds like I am overlooking something simple. 

Investment transactions are in QIF format already so was hoping to just perform one import of QIF that was combined of both and call it a day instead of performing two separate imports, QIF for transactions and CSV for pricing. I could even live with last prices that were used from the last transaction but that isn’t being used to display total amount for either. I can live with two imports once I figure out what the data should be in for "From Namespace" column needs… 

From: D. <sunfish62 at yahoo.com <mailto:sunfish62 at yahoo.com> > 
Sent: Tuesday, September 07, 2021 2:42 PM
To: David Carlson <david.carlson.417 at gmail.com <mailto:david.carlson.417 at gmail.com> >
Cc: Kalpesh Patel <kalpesh.patel at usa.net <mailto:kalpesh.patel at usa.net> >; David Reiser via gnucash-user <gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org> >
Subject: Re: [GNC] QIF imports for security pricing 

I've used csv imports for commodity prices many times. Because of problems with retrieving prices through Finance::Quote that started when Yahoo! deactivated its servers, I set up a process that worked pretty well to get weekly price updates into gnucash. 

I built a spreadsheet in Google Sheets in which I pasted the output of the Advanced Portfolio report on one sheet. I then pulled tickers from this pasted info, and then used Google's finance functions to get current quotes. This all was in a sheet with stock symbol, date, and price, that I could then cut and paste into a new csv file, which I then imported into gnucash using a saved csv profile. 

Although it sounds complex, it actually only took a couple of minutes to perform the routine, once I had it worked through. 

David T. 

  _____  

From: David Carlson 
Sent: Tue Sep 07 10:32:21 EDT 2021
To: Kalpesh Patel 
Cc: Gnucash Users 
Subject: Re: [GNC] QIF imports for security pricing

Kalpesh,

Have you tried to put together a way to get prices in a CSV file and import
that?  I will admit that I haven't tried that myself, so if any others
could elaborate on a way to do that?

On Tue, Sep 7, 2021 at 8:56 AM Kalpesh Patel <kalpesh.patel at usa.net <mailto:kalpesh.patel at usa.net> > wrote:

 I am at version 4.4 (2020-12-28) and had to roll back to version 4.4
 because
 it appears that the changes in the later versions of libofx included in the
 subsequent version of GnuCash is severely impacting OFX imports for
 investment accounts.

 Securities themselves exist under "FUND" section of security editor and
 were
 created as part of the investment transactions import.

 Looks like I will have to make a choice ... loose certain functionality of
 OFX and upgrade, or live without QIF security pricing import and stay
 behind. A dreaded conundrum ...

 -----Original Message-----
 From: Dong Lin <c54e-gnucash at yahoo.com <mailto:c54e-gnucash at yahoo.com> >
 Sent: Monday, September 06, 2021 3:56 PM
 To: Kalpesh Patel <kalpesh.patel at usa.net <mailto:kalpesh.patel at usa.net> >
 Cc: gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org> 
 Subject: Re: [GNC] QIF imports for security pricing

 Which GNC version are you using? Price database update from QIF imports is
 added in version 4.5.

 In order to propagate the price db through import, you need to ensure the
 security account type is mutual_fund or stock and the corresponding
 security
 exists in the security editor.

 Hello GnuCash Dev and user community -

 Looking for answer to few questions related to GnuCash use, so I am
 hoping community can provide guidance.

 -          Recently I tried to import a QIF file containing mutual fund
 transactions and pricing info into an investment account. The
 securities transactions in it imported fine but it appears that the
 importer skipped security prices that were also defined in that file.
 I tried same file with Quicken 2017 to make sure there were no errors
 in the file and it imported all data without any problem, and with a
 brand new clean GnuCash file and importing it. Price database do not
 show any pricing information either.
 Is
 this the intended behavior of the importer?

 -          After importing transactions, I noticed that Present (USD),
 Cleared (USD), Reconciled (USD), and total (USD) columns in the
 account list displays zero for the value of the account or the
 individual security sub-account. Since these are non-public mutual
 funds, I am unable to get them downloaded via any of the financial
 sources nor do symbol exist for them. Is this the intended behavior?
 How should symbol for each mutual fund be specified? Is there a way to
 tell it to use the last provided price for the mutual fund to
 calculate total for that account and show in at least one of those
 columns?

 -          Where can I find what is expected in the format of import
 prices
 from csv mechanism? Specifically I am getting tripped up with "From
 Namespace" column. I RTFM but I couldn't make sense out of this
 specific field.  If there is any guidance on simply importing pricing
 via CSV that has three columns - date, mutual fund name, and price for
 that day - it would be appreciated.

 Look forward to replies.

 Thanks.

  _____  

 gnucash-user mailing list
 gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org> 
 To update your subscription preferences or to unsubscribe:
 https://lists.gnucash.org/mailman/listinfo/gnucash-user
 If you are using Nabble or Gmane, please see
 https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
 -----
 Please remember to CC this list on all your replies.
 You can do this by using Reply-To-List or Reply-All.

  _____  

 gnucash-user mailing list
 gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org> 
 To update your subscription preferences or to unsubscribe:
 https://lists.gnucash.org/mailman/listinfo/gnucash-user
 If you are using Nabble or Gmane, please see
 https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
 -----
 Please remember to CC this list on all your replies.
 You can do this by using Reply-To-List or Reply-All.