Developing ElliottBrowser for Quandl.Com - 4

Applying Elliott Wave Analysis to a Chart

    Readers may have been heard of the Elliott Wave Principle. The purpose of this post is not to talk about the principle itself or some controversial topics about it, but to introduce two technical analysis tools based on the methods described in 'Mastering Elliott Wave', a Glenn Neely's book.

    Let's proceed directly with the tools. The two differ in how they define mono-waves. Of course, according to the book, there is only one way to define them, and one of the tools adopts it. The other tool uses the concept of 'fractals' in the book 'Trading Chaos' by Bill Williams, and it considers a mono-wave as a wave between a fractal and its adjacent opposite fractal.

    In terms of ElliottBrowser, they are technical indicators TiESPN (Technical indicator for Elliott wave Structure & Progress Notation) and TiESPNfr, respectively, and both analyze given chart data to produce SP (Structure & Progress) notations of each mono-wave in the chart. And both include the Elliott oscillator, i.e., MACD(5, 34, 5) as an auxiliary indicator. See Fig 1 and 2.

Fig 1. ESPN, Alphabet Inc. 2017.08.28

Fig 2. ESPNfr, Alphabet Inc. 2017.08.28

    This post will not treat the details on how TiESPN and TiESPNfr work, but will cover briefly how to use them, Instead. Actually, they are classes included in the library WnFTechnicalIndicators.dll, and which in turn uses WnFElliottWave.dll, the Elliott wave analysis library of Wave And Fractals Co., Ltd.

▷ Some Technical Specifics

    There are 3 ways to apply Elliott wave analysis to a chart data, and it's enough to use one of them.

    1. Using TiESPN or TiESPNfr

using WnFTechnicalIndicators;

    ...
    Chart ch;
    WnFTechnicalIndicators.TI ti;
    ...
    WnFCandles candles = WnFElliottBrowser.Factory.GetCandles(_symbol, _period, CommodityType.None);
    TechnicalIndicator indicator;

    if (ti == TI.ESPN)
    {
        indicator = new TiESPN();
    }
    else if (ti == TI.ESPNfr)
    {
        indicator = new TiESPNfr(new int[]{ 5 });
    }
    
    indicator.Init(candles.DOHLCV);
    ch.DataSource = indicator.Data;

 
    2. Using 'IndicatorSignal' instance

    Along with several technical indicator classes, the library contains the 'IndicatorSignal' class, the usage of which is to raise events (or notification) in real-time environment based on the conditions of its companion technical indicator.

    This method is more general so that we can apply technical indicators, other than TiESPN or TiESPNfr, to a chart.

    Chart ch;
    WnFTechnicalIndicators.TI ti;
    ...
    WnFCandles candles = WnFElliottBrowser.Factory.GetCandles(_symbol, _period, CommodityType.None);
    ...
    string sstr = IndicatorSignal.GetDefaultSigStr(ti, (CandlePeriod)_period);
    IndicatorSignal isig = new IndicatorSignal((CandlePeriod)_period, sstr, ti);
    // Setting a companion technical indicator of the IndicatorSignal instance
    isig.Indicator = IndicatorSignal.IStrToObj(isig.TI, isig.IStr);
    isig.Indicator.Init(candles.DOHLCV);
    ch.DataSource = isig.Indicator.Data;

 
    3. Using WnFElliottWave.dll directly

    WnFElliottWave.dll library contains 2 classes, ElliottWave and MacroWave. ElliottWave corresponds to a mono-wave, and MacroWave represents a collection of linked mono-waves.

    Given a line chart, a MacroWave instance finds mono-waves and analyzes them based on the methods described in the Glenn Neely's book 'Mastering Elliott Wave'.

using WnFElliottWave;

    ...
    Chart ch;
    WnFCandles candles = WnFElliottBrowser.Factory.GetCandles(_symbol, _period, CommodityType.None);
    
    candles.DOHLCV.Columns.Add("ESPN", typeof(double), "(High+Low)/2");   // Mean Price
    candles.DOHLCV.Columns.Add("ESPN_SP", typeof(string));                // SP notations

    MacroWave eph = new MacroWave();
    eph.mplist = candles.DOHLCV.AsEnumerable().Select(r => r.Field("ESPN")).ToArray();
    eph.monoize();
    eph.analyze(WnFElliottWave.EPAM.DE);
    
    foreach (ElliottWave li in eph.mw_list.Values)
    {
        candles.DOHLCV.Rows[li.liS]["ESPN_SP"] = li.mSP;
    }
    
    ch.DataSource = candles.DOHLCV;

    There are 3 modes of analysis, which are typed to the enum WnFElliottWave.EPAM. See below.

namespace WnFElliottWave
{
    [Guid("F021AB8E-F16D-4EB0-A268-8C26A8E6C812"), ComVisible(true)]
    public enum EPAM
    {
        DE = 0,    // Default - Analyzes all mono-waves
        SI = 1,    // Skips very small, i.e., non-similar mono-waves
        DI = 2     // Analyzes directional movements
    }
}

Developing ElliottBrowser for Quandl.Com - 3

Downloading / Storing Chart Data Incrementally - 3

    In this blog post, let's talk about the database usage of ElliottBrowser. As noted before, it's necessary to use a local data store to enable the idea of 'incremental download', or 'fresh-download first and append later'.

    Then, what would we use as a local data store?  It may be that the most preferable choice is to use a RDBMS such as MySQL, PostgreSQL, or to use a NoSQL database. But because the data structure under our consideration, actually time series in stock charts, is too simple and formal, using a database system may be too much. Even so, we cannot store our data to some local files while caring about all IO and some additional processing.

    Assuming we will use a database system, but not limited to a specific one, and let's talk about what we need more in that case. The main point of view is how to reduce the dependency to a specific database while allowing as many preferences to databases as possible to developers.

▷ Functional Design for Database Accesses

    1. ElliottBrowser adopts a wrapper class, 'WnFDbConnectionWrapper', so that we can respond easily to database changes. ※ For those who want to use databases other than MS SQL Server Compact, it's enough to implement an subclass of the wrapper class.

    2. We keep the wrapper class most simple. Its functions are as follows
       - Creating a wrapper object per database (F.Req.1)
       - Getting the last candlestick per chart data, if any (F.Req.2)
       - Inserting / appending / getting candlesticks per chart data (F.Req.3~5)

▷ Some Technical Specifics

    1. Abstracting database access

public enum WnF_DBType
{
    SQLCE = 0,
    MySQL = 1,
    SQLite = 2
}

public class WnFDbConnectionWrapper : IDisposable
{
    public WnFDbConnectionWrapper(WnF_DBType k, string s)
    {
        if (string.IsNullOrEmpty(s))
            throw new ArgumentException("Connection String Empty", "connStr");
        connStr = s;
        type = k;
    }

    // F.Req.1
    public static WnFDbConnectionWrapper GetWrapper(WnF_DBType k, string s)
    {
        WnFDbConnectionWrapper wrpper = default(WnFDbConnectionWrapper);

        if (k == WnF_DBType.SQLCE)
        {
    retry:
            wrpper = new SqlCeWrapper(k, s);
            if (!wrpper.InitConnection())
            {
                wrpper = null; Thread.Sleep(10);
                goto retry;
            }
        }
        else
            throw new NotImplementedException();

        return wrpper;
    }

    #region " IDisposable Support "
    /// Keep track of when the object is disposed. 
    protected bool disposed = false;
    /// This method disposes the base object's resources. 
    protected virtual void Dispose(bool disposing)
    {
        if (!this.disposed)
        {
            if (disposing)
            {
                /// Insert code to free managed resources.
                if ((conn != null)) conn.Close();
            }
            /// Insert code to free unmanaged resources. 
        }
        this.disposed = true;
    }

    /// Do not change or add Overridable to these methods. 
    /// Put cleanup code in Dispose(ByVal disposing As Boolean). 
    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }
    #endregion

    public DbConnection Connection
    {
        get { return conn; }
    }

    public bool InitConnection()
    {
        SetupConnection();
        return (conn != null);
    }

    // F.Req.2
    public StockOHLCV CheckTable(int p, string s, out string sn)
    {
        StockOHLCV lastC = default(StockOHLCV);
        sn = TableName(p, s);
        if (Exists(sn))
            lastC = LastRow(sn);
        else
            CreateTable(sn, p);
        return lastC;
    }

    // F.Req.3
    public virtual void InsertTable(DataTable dt) { }

    // F.Req.4
    public int AppendTable(DataTable dt)
    {
        if (!DeleteRow(dt.Rows[0])) throw new Exception("DeleteRow failed");
        InsertTable(dt);
        return dt.Rows.Count;
    }

    // F.Req.5
    public virtual int FillRows(int p, string s, int n, ref DataTable dt_in)
    {
        return 0;
    }


    protected WnF_DBType type;
    protected string connStr;
    protected DbConnection conn;


    protected virtual void SetupConnection() { }

    protected virtual bool Exists(string cname)
    {
        return false;
    }

    protected virtual StockOHLCV LastRow(string cname)
    {
        return default(StockOHLCV);
    }

    protected virtual void CreateTable(string cname) { }

    protected string TableName(int p, string s)
    {
        string t = null;
        int i = 0;
        if (int.TryParse(s, out i))
            t = "_" + s;
        else
            t = s;
        t = t.Replace(".", "D_").Replace("#", "_S_").Replace("@", "_AT_").Replace("-", "_DS_") + "_" + p;
        return t;
    }

    protected bool DeleteRow(DataRow dr)
    {
        bool b = true;
        string sql = "delete from " + dr.Table.TableName + " where DateTime Like '"
                                    + ((string)dr["DateTime"]).Substring(0, 10) + "%'";

        try
        {
            using (DbCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine("[WnFDbConnectionWrapper.DeleteRow()] Exception: " + ex.Message);
            b = false;
        }
        return b;
    }
}

    

    2. Wrapper for MS SQL Server Compact

public class SqlCeWrapper : WnFDbConnectionWrapper
{
    public SqlCeWrapper(WnF_DBType k, string s) : base(k, s)
    {
    }

    protected override void SetupConnection()
    {
        try
        {
            string strds = connStr.Split(';')[0];
            strds = strds.Split('=')[1];
            if (!File.Exists(strds))
            {
                SqlCeEngine engine = new SqlCeEngine(connStr);
                engine.CreateDatabase();
            }
            conn = new SqlCeConnection(connStr);
            conn.Open();
        }
        catch (Exception ex)
        {
            Console.WriteLine("Exception at SqlCeWrapper.SetupConn()\r\n" + ex.Message);
            if (conn != null)
            {
                conn.Close();
                conn = null;
            }
        }
    }

    protected override bool Exists(string cname)
    {
        bool b = false;
        if (cname != string.Empty)
        {
            SqlCeCommand mycommand = ((SqlCeConnection)conn).CreateCommand();
            string sql = "SELECT Count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='" + cname + "'";
            mycommand.CommandText = sql;
            b = (Convert.ToInt32(mycommand.ExecuteScalar()) > 0);
        }
        return b;
    }

    protected override StockOHLCV LastRow(string cname)
    {
        StockOHLCV lastC = default(StockOHLCV);
        string sql = "select * from " + cname + " where DateTime IN (select MAX(DateTime) from " + cname + ")";
        SqlCeCommand mycommand = ((SqlCeConnection)conn).CreateCommand();
        mycommand.CommandText = sql;
        try
        {
            lastC = new StockOHLCV(Convert.ToString(mycommand.ExecuteScalar()), 0, 0, 0, 0, 0);
        }
        catch (Exception ex)
        {
            Console.WriteLine("Exception at SqlCeWrapper.LastItem()\r\n" + ex.Message);
        }
        return lastC;
    }

    public override void InsertTable(System.Data.DataTable dt)
    {
        using (SqlCeCommand cmd = new SqlCeCommand())
        {
            cmd.Connection = (SqlCeConnection)conn;
            cmd.CommandText = dt.TableName;
            cmd.CommandType = CommandType.TableDirect;

            using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable))
            {
                foreach (DataRow r in dt.Rows)
                {
                    SqlCeUpdatableRecord record = rs.CreateRecord();
                    foreach (DataColumn col in dt.Columns)
                        record.SetValue(dt.Columns.IndexOf(col), r[col]);
                    rs.Insert(record);
                }
            }
        }
    }

    private string _create_fields()
    {
        return " (DateTime NVARCHAR(19) PRIMARY KEY, [Open] REAL, High REAL, Low REAL, [Close] REAL, Volume REAL)";
    }

    protected override void CreateTable(string cname)
    {
        string sql = "create table " + cname + _create_fields();
        SqlCeCommand mycommand = ((SqlCeConnection)conn).CreateCommand();
        int rcnt = 0;

    ReCreateSqlCe:
        try
        {
            using (SqlCeTransaction trn = ((SqlCeConnection)conn).BeginTransaction())
            {
                mycommand.CommandText = sql;
                mycommand.Transaction = trn;
    RetrySqlCe:
                try
                {
                    mycommand.ExecuteNonQuery();
                    trn.Commit();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception at SqlCeWrapper.CreateTable(), Retry count " + rcnt + "\r\n" + ex.Message);
                    if ((trn != null)) trn.Rollback();
                    if (rcnt < 3)
                    {
                        rcnt += 1;
                        Thread.Sleep(2000);
                        goto RetrySqlCe;
                    }
                }
            }
        }
        catch (SqlCeException ex)
        {
            if (Strings.InStr(ex.Message, "locked") > 1)
            {
                Thread.Sleep(1000);
                goto ReCreateSqlCe;
            }
        }
    }

    public override int FillRows(int p, string s, int n, ref DataTable dt_in)
    {
        int ccnt = 0;
        string sql = "select TOP (" + n + ") * from " + s + " order by DateTime desc";
        SqlCeCommand mycommand = ((SqlCeConnection)conn).CreateCommand();
        mycommand.CommandText = sql;

        SqlCeDataAdapter da = new SqlCeDataAdapter(mycommand);
        DataColumn col = new DataColumn("row_num", typeof(Int32));
        col.AutoIncrement = true;
        col.AutoIncrementSeed = 0;
        dt_in.Columns.Add(col);

        DataTable dt = new DataTable();
        DataTableReader dtReader = default(DataTableReader);

        try
        {
            ccnt = da.Fill(dt);
            if (ccnt > 0)
            {
                dt = dt.Select(string.Empty, "DateTime Asc").CopyToDataTable();
                dtReader = new DataTableReader(dt);

                dt_in.BeginLoadData();
                dt_in.Load(dtReader);
                dt_in.EndLoadData();
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine("Exception at SqlCeWrapper.FillCandles()\r\n" + ex.Message);
        }

        return ccnt;
    }

}

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.

    

Developing ElliottBrowser for Quandl.Com - 1

Downloading / Storing Chart Data Incrementally - 1

    If we get chart data locally, i.e., from a local file system or a database, we probably do not have to worry about performance. However, since ElliottBrowser for Quandl.Com fetches necessary data for charting from the Quandl's web sites, the size of data being requested is a big problem.

    Assuming we need a daily chart covering the last 3 years, should ElliottBrowser download 600 or more records every time it connect? What if we need the data not once but very frequently?

▷ Functional Design

    1.  JSON rather than XML - Quandl.Com supports both, but we use JSON.   ※ Note: JSON vs XML

    2.  Incremental Download - For a given stock, its chart data once downloaded, the next time ElliottBrowser connects to Quandl it downloads only the latest records which need to be updated or added.

    3. Database essential to Incremental Download - The downloaded data is stored in a database as a reliable data store.

▷ Some Technical Specifics

    Let's take a look at the Quandl.Com's JSON response format for chart data requests. ※ Note: Check the HTTP request URL format for Quandl.Com

Fig 1. Quandl's JSON Response Format for Chart Data

    We need to transform the JSON response string into a data type that fits our purpose. Of course, our goal is to store the time series data contained in the string in a database or display it as a chart with some additional analysis data.

    1. Deserializing JSON string using Json.NET

    We will use one of JsonSerializer.Deserialize methods, which deserializes the JSON structure contained by a reader into an instance of the specified type. See the API document.

 public Object Deserialize(
  JsonReader reader,
  Type objectType
 )

    Let's define a data type representing the JSON string returned by Quandl. As shown in Fig 1, the whole response string is a set of only one element which is actually a key-value pair with key 'dataset_data', value part in turn being a set containing the time-series 'data'. We map the root-level set into 'QuandlDataWrapper' type, and the value part of the key-value pair into 'QuandlData' type. See below.

    public class QuandlDataWrapper
    {
        public QuandlData dataset_data
        {
            get { return _dataset_data; }
            set { _dataset_data = value; }
        }

        private QuandlData _dataset_data;
    }

    public class QuandlData
    {
        public string limit
        {
            get { return _limit; }
            set { _limit = value; }
        }

        public string transform
        {
            get { return _transform; }
            set { _transform = value; }
        }

        public int[] column_index
        {
            get { return _column_index; }
            set { _column_index = value; }
        }

        public string[] column_names
        {
            get { return _column_names; }
            set { _column_names = value; }
        }

        public string start_date
        {
            get { return _start_date; }
            set { _start_date = value; }
        }

        public string end_date
        {
            get { return _end_date; }
            set { _end_date = value; }
        }

        public string frequency
        {
            get { return _frequency; }
            set { _frequency = value; }
        }
        
        //
        // The property representing the time-series which is labeled with 'data'
        // "data":
        // [
        //  ["2017-08-23",159.07,160.47,158.88,159.98,19198189.0],
        //  ["2017-08-22",158.23,160.0,158.02,159.78,21297812.0],
        //  ["2017-08-21",157.5,157.89,155.1101,157.21,26145653.0]
        // ],
        //
        public object[] data
        {
            get { return _data; }
            set { _data = value; }
        }
        
        public string collapse
        {
            get { return _collapse; }
            set { _collapse = value; }
        }

        public string order
        {
            get { return _order; }
            set { _order = value; }
        }

        private string _limit;
        private string _transform;
        private int[] _column_index;
        private string[] _column_names;
        private string _start_date;
        private string _end_date;
        private string _frequency;
        private object[] _data;
        private string _collapse;
        private string _order;
    }

 
    The 'data' property of QuandlData represents the time-series chart data and currently is of type array of objects, each element of which itself an array of mixed types, i.e., a string for a date and multiple numbers.

    These two type definitions, QuandlDataWrapper and QuandlDate, can fulfill our deserialization task of Quandl.Com's JSON string. But in runtime, ElliottBrowser must convert the time-series object, actually of JArray type, into an array of strings to iterate and get the numbers which correspond to a quote data of a specific date. By defining a simple JsonConverter and a type, let's say 'TimeSeries', we can avoid such a hassle by changing the type of property 'data' from array of objects to array of TimeSeries'.

    [JsonConverter(typeof(TimeSeriesConverter))]
    public class TimeSeries
    {
        public string Date { get; set; }
        public double[] Values { get; set; }
    }

    class TimeSeriesConverter : JsonConverter
    {
        public override bool CanConvert(Type objectType)
        {
            return (objectType == typeof(TimeSeries));
        }

        public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
        {
            JArray ja = JArray.Load(reader);
            TimeSeries ts = new TimeSeries();
            ts.Date = (string)ja[0]; ja.RemoveAt(0);
            ts.Values = ja.Select(jv => (double)jv).ToArray();
            return ts;
        }

        public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
        {
            JArray ja = new JArray();
            TimeSeries ts = (TimeSeries)value;
            ja.Add(ts.Date);
            ja.Add(ts.Values);
            ja.WriteTo(writer);
        }
    }

 
    Since defining the required data types is over, let's get our feet wet. First, we deserialize the response string,

    public static object GetJSONObjects(string url, Type t)
    {
        WebRequest request = default(WebRequest);
        WebResponse response = default(WebResponse);
        object result = null;
        JsonSerializer s = new JsonSerializer();

        request = WebRequest.Create(url);
        response = request.GetResponse();

        if (((HttpWebResponse)response).StatusDescription != "OK")
        {
            Console.WriteLine(((HttpWebResponse)response).StatusDescription);
        }

        using (Stream dataStream = response.GetResponseStream())
        {
            using (StreamReader reader = new StreamReader(dataStream))
            {
                using (JsonReader jrdr = new JsonTextReader(reader))
                {
                    result = s.Deserialize(jrdr, Type.GetType(t.FullName));
                }
            }
        }

        response.Close();
        return result;
    }

    and, store time-series data to a DataTable object.

    public QuandlError GetCandlesTable(string fstr, string end_date, out DataTable dic, string tname = "")
    {
        QuandlError err = default(QuandlError);
        QuandlDataWrapper result = default(QuandlDataWrapper);
        DataRow r = default(DataRow);
        string req_url = string.Format(fstr, end_date);
        int ccnt = 0, rcnt = 0;
        dic = _candles_table(tname);

        try
        {
            result = (QuandlDataWrapper)WnFElliottBrowser.GetJSONObjects(req_url, typeof(QuandlDataWrapper));
        }
        catch (WebException ex)
        {
            int status = (int)((HttpWebResponse)ex.Response).StatusCode;
            err = GetQuandlError(ex);
            Console.WriteLine("Exception at QuandlAPI.GetCandlesTable() GetJSONObjects returned {0}", status);
        }

        rcnt += 1;
        ccnt = result.dataset_data.data.Length;
        for (int i = result.dataset_data.data.Length-1; i>= 0; i--)
        {
            TimeSeries v = result.dataset_data.data[i];
            r = dic.NewRow();
            r["DateTime"] = v.Date.Replace("-", "/");
            r["Open"] = v.Values[0];
            r["High"] = v.Values[1];
            r["Low"] = v.Values[2];
            r["Close"] = v.Values[3];
            r["Volume"] = v.Values[4];
            dic.Rows.Add(r);
        }

        result = null;
        return err;
    }

    private DataTable _candles_table(string tname = "")
    {
        DataColumn col;
        DataTable dohlcv = new DataTable();
        if (string.IsNullOrEmpty(tname))
        {
            col = new DataColumn("row_num", typeof(Int32));
            col.AutoIncrement = true;
            col.AutoIncrementSeed = 0;
            dohlcv.Columns.Add(col);
        }
        else
            dohlcv.TableName = tname;

        dohlcv.Columns.Add("DateTime", typeof(string));
        dohlcv.Columns.Add("Open", typeof(double));
        dohlcv.Columns.Add("High", typeof(double));
        dohlcv.Columns.Add("Low", typeof(double));
        dohlcv.Columns.Add("Close", typeof(double));
        dohlcv.Columns.Add("Volume", typeof(double));
        dohlcv.PrimaryKey = new DataColumn[] { dohlcv.Columns["DateTime"] };
        return dohlcv;
    }

    ※ Next blog post will cover database-related content.

 

Developing ElliottBrowser for Quandl.Com - Intro

▶ Brief description of ElliottBrowser

    ElliottBrowser is a tool for Elliott wave analysis, mainly based on the methods described in the Glenn Neely's book, 'Mastering Elliott Wave'.

    It's main functionality is to display 'structure-and-progress notations' of waves in chart data of stocks - See Fig 1.

    ElliottBrowser for Quandl.Com uses free data gathered from Quandl.Com, especially its WIKI database.

Fig 1. ElliottBrowser

▶ Usecases

    - Downloading / storing the list of the stocks in Quandl.Com's WIKI database
    - Refreshing the list when needed
    - Displaying the list
    - Downloading / storing chart data incrementally on-demand
    - Applying Elliott wave analysis to extract structure-and-progress notations of waves
    - Charting

▶ Prerequisites

   - .Net framework 4.5 or above
    - Microsoft SQL Server Compact 4.0
    - Visual Studio Community 2015
    - Quandl.Com account

Storing / Refreshing the List of the Stocks in WIKI database

    When a user using ElliottBrowser wants to view a chart of a stock, he first needs to lookup its ticker symbol (or shortly symbol) or to select an item in the list of stock symbols. So, ElliottBrowser provdes a panel for that purpose. See Fig 2.

Fig 2. The Panel of Symbols

    Quandl.Com provides the list of stock symbols in the WIKI database through the URL "https://www.quandl.com/api/v3/databases/WIKI/codes.json". When accessing the URL using a web browser, it saves the response data as a .zip file rather than displaying it as some form of JSON string.

    To prepare the panel of symbols, should ElliottBrowser download the .zip file and unzip to extract the symbols every time it is starting? It seems to be reasonable. But, what if the .zip file in Quandl.Com remain unchanged for a while?

▷ Functional Design

    1. When ElliottBrowser accesses Quandl.Com for the first time, it downloads and stores the .zip file containing the list of stock symbols to a local disk as it is. Of course, it should extract the contents of the .zip file and prepare the panel of symbols.

    2. After that, when it is starting, it initiates 2 threads, one for extracting symbols from the local .zip file to prepare the panel, and the other for fetching the remote .zip file to compare it with the local file. When they mismatch, the second thread saves the new .zip file and notifies so that ElliottBrowser can refresh the panel of symbols.

▷ Some Technical Specifics

    1. How to get the list of symbols

    The .zip file contains only one .csv file with each line corresponding to a symbol-description pair representing a stock as Fig 3 below. To get the list of symbols, we parse each line into a key-value pair, which will be added as an element in a dictionary data type.

Fig 3. Contents extracted from the .zip file

    private Dictionary _zipped_CSV_to_dic(ZipArchive za)
    {
        Dictionary dic = new Dictionary();
        using (var unzipped = za.Entries[0].Open())
        {
            using (var reader = new StreamReader(unzipped, Encoding.ASCII))
            {
                string line, symbol, name;
                string[] arr, arr1;
                dic = new Dictionary();

                while ((line = reader.ReadLine()) != null)
                {
                    arr = line.Trim().Split(',');
                    arr1 = arr[0].Split('/');
                    symbol = arr1.Length > 1 ? arr1[1] : arr[0];
                    name = line.Replace(arr[0] + ",", string.Empty).Replace("\"", string.Empty)
                                .Replace("Prices, Dividends, Splits and Trading Volume", string.Empty).Trim();
                    dic[symbol] = name;
                }
            }
        }
        return dic;
    }

    2. Downloading and comparing the .zip file with the local .zip previously stored

    The .zip file is fetched as a content in a HttpWebResponse, and is extracted to get the .csv file using ZipArchive class in System.IO.Compression library in .Net 4.5. After that, using MD5 hash in System.Security.Criptyography library, the .csv is compared with that contained in the previously stored .zip file.

    delegate void d_refreshSymbols(string symbols_fstr, string database, ref Dictionary dic, ref int err);

    private void _refresh_symbols(string symbols_fstr, string database, ref Dictionary dic, ref int err)
    {
        string url, path;
        HttpWebResponse response = default(HttpWebResponse);
        url = string.Format(symbols_fstr, database);

        var webRequest = (HttpWebRequest)WebRequest.Create(url);
        try
        {
            response = webRequest.GetResponse() as HttpWebResponse;

            if (response.ContentType == "application/zip")
            {
                bool to_serialize = true, overwrite = false;
                path = _home + Path.DirectorySeparatorChar + database;
                if (!Directory.Exists(path)) Directory.CreateDirectory(path);
                path += Path.DirectorySeparatorChar + response.ResponseUri.AbsolutePath.Substring(1);
                Properties.Settings.Default.symbolsPath = path; Properties.Settings.Default.Save();

                using (var mstr = new MemoryStream())
                using (var str = response.GetResponseStream())
                {
                    str.CopyTo(mstr);
                    ZipArchive zipArchive = new ZipArchive(mstr, ZipArchiveMode.Read);

                    if (File.Exists(path))
                    {
                        using (Stream input = File.OpenRead(path))
                        {
                            var serialized = new ZipArchive(input, ZipArchiveMode.Read);
                            using (var md5 = MD5.Create())
                            {
                                var h1 = System.Text.Encoding.UTF8.GetString(md5.ComputeHash(zipArchive.Entries[0].Open()));
                                var h2 = System.Text.Encoding.UTF8.GetString(md5.ComputeHash(serialized.Entries[0].Open()));
                                to_serialize = overwrite = (h1 != h2);
                            }
                        }
                    }

                    if (to_serialize)
                    {
                        mstr.Position = 0;
                        using (Stream output = File.OpenWrite(path))
                            mstr.CopyTo(output);

                        if (overwrite)
                        {
                            if (SymbolsUpdate != null) SymbolsUpdate(); err = 0; goto step1;
                        }
                    }

                    dic = _zipped_CSV_to_dic(zipArchive);
                }
    step1:
                err = 0;
            }
            else
            {
                err = (int)APIError.Unknown;
            }
        }
        catch (WebException ex)
        {
            QuandlError qerror = GetQuandlError(ex);
            err = qerror.ToInt();
        }
    }

 

    3. Getting symbols from local, refreshing with one from Quandl

    Once the .zip file being stored locally, ElliottBrowser prepares its panel of symbols using the local file first, while invokes a delegate to check the mismatch between the local and the remote .zip.

    public void RefreshSymbols(out Dictionary dic, out int err)
    {
        string ppath; // The path to the previously stored .zip file
        symbols_fstr = "https://www.quandl.com/api/v3/databases/{0}/codes.json";
        string database = "WIKI";
        dic = new Dictionary();
        err = 0;

        ppath = Properties.Settings.Default.symbolsPath;
        if (!string.IsNullOrEmpty(ppath))
        {
            if (File.Exists(ppath) && !File.GetAttributes(ppath).HasFlag(FileAttributes.Directory))
            {
                string[] parr = ppath.Split(Path.DirectorySeparatorChar);
                if (parr[parr.Length - 2] == database)
                {
                    using (Stream input = File.OpenRead(ppath))
                    {
                        ZipArchive zipArchive = new ZipArchive(input, ZipArchiveMode.Read);
                        dic = _zipped_CSV_to_dic(zipArchive);
                    }

                    d_refreshSymbols d = _refresh_symbols;
                    d.BeginInvoke(symbols_fstr, database, ref dic, ref err, null, null);
                    err = 0; return;
                }
            }
        }

        _refresh_symbols(symbols_fstr, database, ref dic, ref err);
    }

※  The sources of ElliottBrowser for Quandl.Com and its necessary libraries have been published in a GitHub repository.