Developing ElliottBrowser for Quandl.Com - 2

Downloading / Storing Chart Data Incrementally - 2

    In the last blog post, I covered deserialization of JSON string into a data type defined in ElliottBrowser. This post deals with the process of storing time-series data into a database.

    Just as ElliottBrowser keeps the list of stocks locally in the form of a .zip file downloaded from Quandl and refreshes the file whenever it is required, it treats chart data in a similar fashion except that the data is stored in a database.

    First, let us consider the use case. When a user select a ticker symbol in the ElliottBrowser's panel of symbols, ElliottBrowser fetches the chart data of the corresponding stock. When there's no data of the stock being kept by ElliottBrowser itself, it downloads them from Quandl.Com as many as possible (fresh-download). If the data is of significant size, the user should wait for a while to see the chart.

  How do we reduce the users' waiting time, which they may feel bored, while downloading the data? It's simple. Let's hide the download process into the background, and display the requested chart with only a small amount of data.

    If the user has viewed the stock chart at least once, the data of the stock has been stored in the database, so it is enough to download only the changed part of the data (append).

 

▷ Some Technical Specifics

    1. 'StockOHLCV', a type representing a bar (or candlestick) in a stock chart with volume

public struct StockOHLCV
{
    public string D;
    public double O;
    public double H;
    public double L;
    public double C;
    public double V;

    public StockOHLCV(string _d, double _o, double _v)
    {
        D = _d; O = _o; H = _o; L = _o; C = _o; V = _v;
    }

    public StockOHLCV(string _d, double _o, double _h, double _l, double _c, double _v)
    {
        D = _d; O = _o; H = _h; L = _l; C = _c; V = _v;
    }
}

    2. 'WnFCandles', a type representing the 'chart data' of a stock

    We will use a System.Windows.Forms.DataVisualization.Charting.Chart object for charting, DataSource property of which needs to be set in runtime. For the data type of the DataSource, DataTable is enough.

    When we instantiate an WnFCandles object for a stock chart, let's mark the last candlestick of the chart data, if any, stored in the database for the purpose to decide whether to fresh-download or to append.

public class WnFCandles
{
    protected int _period;
    protected string _stockcode;    // the ticker symbol of the stock
    protected DataTable _dohlcv;    // the whole candlesticks to be set as the DataSource
    protected StockOHLCV _last;     // the last candlestick in the chart
    ...

    public WnFCandles(int p, string s)
    {
        if (!ValidPeriod(p)) throw new ArgumentException("Invalid candle period " + p);
        _period = p;
        _stockcode = s;
    }

    public string Symbol
    {
        get { return _stockcode; }
    }

    public int Period
    {
        get { return _period; }
        set { _period = value; }
    }

    public DataTable DOHLCV
    {
        get { return _dohlcv; }
        set
        {
            if (value == null) throw new ArgumentNullException();
            if (value.Rows.Count == 0) throw new ArgumentException("Candles vacant");
            _dohlcv = value;
        }
    }

    ...
}

    

    3. Separating first-time charting and fresh download

    There are some more things to talk about, such as an interface type and a database connection wrapper class, but let's focus only on the use case mentioned above.

    In response to a user's request for a chart, on instantiating an WnFCandles object for the chart data, ElliottBrowser checks if the last candlestick is set. If it's the case, ElliottBrowser downloads recent data first and displays a chart. But if not, it invokes a separate thread which starts fresh download followed by database table insertion, and its main thread downloads small portion of data to display a chart. See 'GetCandles()' method below.

public class QuandlChart : WnFCandles
{

    public QuandlChart(int p, string dataset, string database, WnFDbConnectionWrapper _wrpper = null) : base(p, dataset)
    {
        _database = database;
        _api = (QuandlAPI)WnFElliottBrowser.Factory;
        _set_dbconn(_wrpper);
        _init_candles();
    }
    
    ...
    
    private QuandlAPI _api;
    private string _database;
    private WnFDbConnectionWrapper _dbc_wr;
    private bool _dbc_wr_owner;
    private string _tname;

    public int GetCandles()
    {
        QuandlAPI.QuandlError err = default(QuandlAPI.QuandlError);
        DataTable dt = default(DataTable);
        bool append;
        string d1, d2;
        int no_candles = 0;
        string fstr;
        
        string fstr;
        if (!_api.ReadURLString("Candles", out fstr))
        {
            MessageBox.Show("Error reading URL format string for candles.", Properties.Settings.Default.tm, MessageBoxButtons.OK);
            return (int)APIError.APIUrlFormat;
        }

        append = _is_append(out d1);
        d2 = DateTime.Today.ToString("yyyy-MM-dd");
        fstr = _api.Get_URL(_database, _stockcode, ((QuandlAPI.QuandlPeriod)_period).ToString(), d1, "{0}", "{1}");

        if (!append)
        {
            Console.WriteLine("[QuandlChart.GetCandles()] calling _store_candles async for " + _stockcode);
            d_store_candles d = _store_candles;
            d.BeginInvoke(fstr.Replace("&rows={1}", ""), d2, null, null);

            err = _api.GetCandlesTable(string.Format(fstr, "{0}", QuandlAPI._MAX_JSON), d2, out _dohlcv);
            return (err == null) ? 0 :err.ToInt();
        }
        else
        {
            if (DateTime.Parse(d2) < DateTime.Parse(d1)) d2 = string.Empty;

            err = _api.GetCandlesTable(fstr.Replace("&rows={1}", ""), d2, out dt, _tname);
            if (err != null)
                return err.ToInt();

            no_candles = (dt.Rows.Count > 0) ? _dbc_wr.AppendTable(dt): 1;
            dt = null;
            return no_candles;
        }
    }
    
    ...
    
    private void _init_candles()
    {
        _last = _dbc_wr.InitCandles(_period, _stockcode, out _tname);
    }

    private bool _is_append(out string d1)
    {
        bool b = false;
        d1 = QuandlAPI._1ST_DATE;

        if (!string.IsNullOrEmpty(_last.D))
        {
            b = true;
            d1 = _last.D.Replace("/", "-");
        }

        return b;
    }

    private void _store_candles(string fstr, string d2)
    {
        DataTable dic = default(DataTable);
        QuandlAPI.QuandlError err = default(QuandlAPI.QuandlError);
        WnF_DBType dbt = (WnF_DBType)Properties.Settings.Default.dbms;
        string cstr;

        err = _api.GetCandlesTable(fstr, d2, out dic, _tname);
        if (err != null)
        {
            Console.WriteLine("[QuandlChart._store_candles()] GetCandlesTable failed...\r\n" + err.message);
            return;
        }

        cstr = string.Format(Properties.Settings.Default.dbConn, _database);
        using (WnFDbConnectionWrapper dbcw = WnFDbConnectionWrapper.GetWrapper(dbt, cstr))
            dbcw.InsertTable(dic);

        dic = null;
    }
}

    ※ Next blog post will cover the database connection wrapper class.