OK, I don't mean for this to be another "how do I get historical stock data" question.
I've already found plenty of readily available sources for that, even neatly exported into direct CSV downloads from the likes of Google Finance and Yahoo and Finviz.
Software I write actively uses handy tools including YahooFinance and Dirk Eddelbuettel's fine Beancounter.
My question is, perhaps especially if anyone's familiar with beancounter, when I query for a single stock's end-of-day prices, how can I make sure all that historical data gets cached (for instance into local text or sqlite files) so as to minimize hitting the web so much and speed up repeat queries overlapping time frames?
Causing a network lookup for today's real-time price if available yet is inevitable, which by itself is fine. My pricehist.sh script combines that with OHLCV prices of a stock N days back, which can also be easily grabbed with eg. wget or curl to the right URL + proper GET variables.
$ ./pricehist.sh VQT 6
VQT 2011-12-14 125.50 125.50 124.43 124.49 20360
VQT 2011-12-13 128.00 128.00 125.28 125.39 24400
VQT 2011-12-12 127.50 127.50 126.36 126.66 9100
VQT 2011-12-09 128.00 128.31 127.82 128.14 12100
In the instance above I would like pricehist.sh to:
- look up current market data for today if any, so closing price is actually last price if session not over. DONE
- Check if rest of requested history for that ticker symbol isn't already stored locally, otherwise:
- Look up VQT history going back 6 days, store it and print completed table. DONE
Despite all my searching I'm still unsure how to get beancounter to simply output one stock's OHLCV table regardless of portfolio...NOT this output:
$ setup_beancounter -l beancounter.stockdata.sqlite
$ beancounter addstock VQT ACPW
$ beancounter backpopulate --prevdate '1 month ago' --date 'today' VQT ACPW
Adding VQT from 20111114 to 20111214
Adding ACPW from 20111114 to 20111214
$ beancounter dayendreport --prevdate '1 month ago' --date 'today' VQT
## ..... hoping for OHLCV table of VQT only but get this instead:
===============================================================================
Profit / loss from 12 Dec 2011 to 14 Dec 2011 abs, rel change
-------------------------------------------------------------------------------
Citigroup, Inc. N USD 2690.00 26.90 2605.00 26.05 -85.00 -3.16%
Exxon Mobil Corpo USD 6039.75 80.53 5958.00 79.44 -81.75 -1.35%
Google Inc. USD 15640.75 625.63 15451.75 618.07 -189.00 -1.21%
International Bus USD 9557.50 191.15 9436.00 188.72 -121.50 -1.27%
-------------------------------------------------------------------------------
Grand Total USD 33928.00 33450.75 -477.25 -1.41%
===============================================================================
Using the Perl Finance module instead, I just don't see where to activate caching, nor what local data files would be used for the storage. Every call to yahoofinance.rb shows me its web traffic in the squid proxy logs. Thousands of EOD queries, often overlapping, over several processing stages of my engine cause these web lookups to be a major bottleneck.
I figure this wheel's got to already be invented, and I'll use a full RDBMS backend if I have to, though that would mean taxing my Ruby, Perl and Bash scripts with MySQL clients.
By comparison, what I built for the Polish stock market upkeeping only text files for each of the roughly 700 symbols and processed only by grep, sed and awk is incredibly fast--almost instantaneous despite thousands of cycles of looping the equivalent scripts--so my software's daily premarket crunching for the GPW exchange is done in minutes not hours.
