[GNC] Using CSV Importer and Google for Commodity Prices

[David T.]

Hello,

I, like many others, have encountered numerous headaches over the last year due to the turmoil around retrieving commodity price quotes. I appreciated being able to retrieve prices on an occasional basis and see, broadly speaking, how my holdings were performing. I bemoaned the loss of Yahoo! as a F::Q price source, and followed the flock to AlphaVantage, and have seen that method fail as well.

I had resigned myself to logging on to my various online accounts occasionally in order to see in general how the portfolio was doing, but I missed the golden era of stock quotes and reporting in GnuCash (2017?). 

I was intrigued by the release of GC3, and its new CSV importer, but wasn’t sure I was all that interested in digging into it as a means of obtaining prices. But then I saw the recent thread of another user getting their prices into GC this way, so I decided to give it a try. What I was shooting for is a system that allows me to reasonably quickly generate a list of prices and put them into the PriceDB.

I thought I’d share with the list the results. 

First off, I am using Google’s financial functions (which retrieves prices based on a ticker symbol) in an online Sheets file. I created a worksheet that pulls ticker symbols from a second worksheet, has a column that uses this symbol to pull a price, and a third column set to calculate today’s date. 

Next, I opened GnuCash, ran the Advanced Portfolio report, copied the entire contents and pasted it directly into my SRC worksheet. Now the PriceDB worksheet is fully populated with all the commodites in my GnuCash file, and will retrieve current prices for all of them. 

Then I copy this data and paste it into a text file and save it with a csv extension. The GnuCash importer then can import these prices; on the configuration page, I had to ensure that it used tabs as the delimiter, followed the correct date formatting, and had all the other settings correct, but once that was done, the importer was able to pull all the prices in quite quickly. Conveniently, the importer skips duplicate entries, which is helpful since the Advanced Portfolio doesn’t aggregate commodities across accounts. Once I had gotten things to work, I was able to save the importer settings to smooth future imports. I expect that this will allow me to update my pricedb on the sporadic basis that I need, without being unduly burdensome. 

This is the point where I send my gratitude to the GnuCash development team—and Geert in particular—for working to rewrite the csv importer. Thank you, Geert!

David

For those who are interested, the Sheets formulas I use are listed below:

For the Ticker Symbol cell, a simple reference to the cell with the ticker symbol from the Advanced Portfolio (SRC is the sheet name of the Advanced Portfolio source data):

=SRC!B3

For the date cell, returning blank if the referenced cell is empty*:

=if(ISBLANK(A3),"",text(TODAY(),"mm-dd-yyyy"))


For the Price cell, returning blank if the referenced cell is empty*:

=if(ISBLANK($A3), "", GOOGLEFINANCE($A3,"Price"))

* By testing if the cell is empty, I can put the same formula into literally hundred of rows. Then, if the number of commodities in my holdings goes up (or down), the sheet will only show information for commodities that are present.