Databases
StackOverflow2013
Postsdbo
POCaching and updating OHLC historical stock quotes locally
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCaching and updating OHLC historical stock quotes locally
    primarykey
    data
    text
    <p>OK, I don't mean for this to be another "how do I get historical stock data" question.</p> <p>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.</p> <p>Software I write actively uses handy tools including YahooFinance and Dirk Eddelbuettel's fine Beancounter.</p> <p>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?</p> <p>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.</p> <pre><code>$ ./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 </code></pre> <p>In the instance above I would like pricehist.sh to:</p> <ul> <li>look up current market data for today if any, so closing price is actually last price if session not over. DONE</li> <li>Check if rest of requested history for that ticker symbol isn't already stored locally, otherwise:</li> <li>Look up VQT history going back 6 days, store it and print completed table. DONE</li> </ul> <p>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:</p> <pre><code>$ 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% =============================================================================== </code></pre> <p>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.</p> <p>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.</p> <p>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.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USMarcos
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCaching and updating OHLC historical stock quotes locally
      1. USMarcos
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload