Latest Holdings

As part of this blog, I plan to list current stock picks generated by my latest model that I have nicknamed the 'ShortWave'. This model is rather basic and is built upon MACD, RSI(4) and ADX. I shall just report its current notional holdings and see how things develop. I have no interest in these shares, and, as part of my policy of 'having no opinion', I often do not even know what the companies do or even what sector they are in.

Epic	BuyPrice	BuyDate
ARM	5.765	29/11/11
BGC	3.65	08/03/12
DRX	5.505	09/12/11
HIK	7.485	02/03/12
IGG	4.759	15/02/12
IMG	6.195	28/02/12
KENZ	4.664	13/03/12
SNR	1.86	15/02/12

MetaStock Conversion Utility

One invaluable tool for model development I haven't mentioned is Metastock. I have used this for many years to 'fine tune' the development of strategies and indicators. I tend to develop and test 'indicators' in Metastock and then convert this logic into C# code.

For the amateur trader, the biggest issue in using Metastock is how to convert data for it. I originally used end-of-day services such as those provided by Paritech, however, I found this somewhat restrictive as I wanted to control what data I converted e.g. FTSE100 or FTSE250 only. Unfortunately, Metastock does not appear keen to open up the workings of its data format and as such I have never found a pure open-source method of converting data for it. At the heart of my conversion code I use a dll called 'Metalib' (http://trading-tools.com)- my version of the dll is very old now (2007) but still does the job - it is proprietary so you will need to buy it.

Points to note in the ConverterEngine method are:

•  The EPIC codes and Prices are pulled in from SQL using a Linq query.
• All existing Metastock data files are deleted to start with.
• There are two parts to the method: OpenSecurityBySymbol and AppendDataRec - the first creates the security file whilst the second adds subsequent price data to it.
• The Sort method just sorts the files by name - the filenames are meaningless gibberish so the Sort method arranges them for when you open them in Metastock.

I use this conversion method with the version 11 of Metastock with no problems. It runs nightly after the price scrape has completed.

  
private readonly MLReader _metaReader;
private readonly MLWriter _metaWriter;
private readonly MLRegistration _mReg;
private MetastockDataContext _db;
private IQueryable _hemsList; 

public void ConverterEngine()  
     {  
       _db = new MetastockDataContext();  
       _db.Connection.ConnectionString = Database.ConnectionString;  
       _hemsList = from epic in _db.StatsLists  
             where epic.FTSE_Index.Replace("\"", "") == "FTSE100"  
             || epic.FTSE_Index.Replace("\"", "") == "FTSE250"  
             orderby epic.EPIC  
             select epic;  
       Console.WriteLine("[][][][][][][][]Doing METASTOCK conversion[][][][][][][][]");  
       _mReg.SetRegistrationInfo(PUT_YOUR_REG_HERE, KEY_HERE);  
       if (!Directory.Exists("C:/Metastock/Securities"))  
       {  
         Directory.CreateDirectory("C:/Metastock/Securities");  
       }  
       foreach (string sFile in Directory.GetFiles("C:/Metastock/Securities"))  
       {  
         File.Delete(sFile);  
       }  
       foreach (StatsList epicItem in _hemsList)  
       {  
         string epic = epicItem.EPIC.Replace("\"", "");  
         IQueryable<Price> prices = from p in _db.Prices  
                       where p.Epic == epic  
                       orderby p.Date ascending  
                       select p;  
         if (prices.Any())  
         {  
           Console.WriteLine("Processing " + epicItem.Share_Name.Replace("\"", ""));  
           foreach (Price price in prices)  
           {  
             DateTime priceDate = price.Date;  
             string yahooDate = priceDate.Year + priceDate.Month.ToString("00") +  
                       priceDate.Day.ToString("00");  
             _metaWriter.OpenDirectory("C:/Metastock/Securities");  
             if (_metaWriter.bSymbolExists[price.Epic])  
             {  
               _metaWriter.OpenSecurityBySymbol(price.Epic);  
               _metaWriter.AppendDataRec(Convert.ToInt32(yahooDate), 0,  
                            Convert.ToSingle(price.Open),  
                            Convert.ToSingle(price.High),  
                            Convert.ToSingle(price.Low),  
                            Convert.ToSingle(price.Close),  
                            Convert.ToSingle(price.Volume),  
                            0f);  
             }  
             else  
             {  
               _metaWriter.AppendSecurity(price.Epic, epicItem.Share_Name.Replace("\"", ""),  
                             PERIODICITY.Daily);  
               _metaWriter.AppendDataRec(Convert.ToInt32(yahooDate), 0,  
                            Convert.ToSingle(price.Open),  
                            Convert.ToSingle(price.High),  
                            Convert.ToSingle(price.Low),  
                            Convert.ToSingle(price.Close),  
                            Convert.ToSingle(price.Volume),  
                            0f);  
               _metaWriter.CloseSecurity();  
             }  
           }  
         }  
       }  
       _metaWriter.CloseDirectory();  
       Sort();  
     }  
     // Private Methods (1)   
     private void Sort()  
     {  
       _metaWriter.OpenDirectory(@"C:\MetaStock\Securities");  
       _metaWriter.Sort();  
       _metaWriter.CloseDirectory();  
     }  

Stock Market Analysis: An Amateur's Perspective

For the many years I have been investing, I have tried to analyse the stocks from a number of angles in attempt to achieve some understanding of what is going on.

I originally started from the perspective of Technical Analysis. I bought the books, studied the charts, read the blogs and eventually came to the conclusion that I might as well be studying tea leaves. My initial foray into investing was driven by whatever 'gut feeling' the charts told me and, I discovered, I was particular unsuccessful at it. I realised that my best defence was to ignore my own 'opinions' and to focus on the facts. For a long time I followed the 'Mechanical Investing' forum on the 'Motley Fool' website which seemed to offer a glimmer of hope in that it's mantra that investment decisions should be based upon a calculated response to inputs rather than personal feeling.

Ultimately, I got into designing 'filters' for fundamental data, especially using such sites as Hemscott (now part of MorningStar) and ShareLockHolmes. I still like fundamental data for choosing stocks, however I believe that larger and quicker gains can only be achieved through price-based means (rightly or wrongly). This is how I ended-up being interested in algorithmic trading and this seems to perfectly balance my interest in software with the mental challenges of developing strategies.

As has been dicussed previously in this blog, I have not yet entered the intra-day realm of trading mainly due to a lack of knowledge and time, although not doubt this will come.

Software and Tools

 I am a self-taught coder. As I have mentioned before, I do not work in the software or finance industries and therefore my code probably lacks the polish and modernity of others.

I like to write most of my code in C#. The reason for this is not just the ease of the language but also the tools that go with it. I have used Java on and off over the years, but have never really liked the tools available (or perhaps understood them). With C# and the whole Microsoft thing, there is just an ease of use and integration that makes life a bit easier for the amateur.

Language choices aside, I write all the software within Visual Studio 10 with the help of a fantastic utility Resharper by Jetbrains to check what I am laying down. With the advent of Linq, I am no longer forced to write stored procedures or other 'off-line' code that cannot be intellisensed. I think Linq is the best thing Microsoft has come up with in a long time!

Another useful utility that comes to mind is Regionerate. This tool will re-layout the order of your code to make it more readable. The tool is a bit old now - it doesn't seem the author supports it any longer, however, the existing version still seems to work with VS10.

I use an MS SQL Server to store data. With some tables being upwards of a million rows, this is the best and easiest way to handle data. I am currently using the *free* version (express I think they call it). You will need to also download the 'management studio' utility to help you build tables etc. MS SQL is an invaluable tool, especially when developing your strategies. It is worth learning some of the basics of the SQL language to help you query the data on file.

On Simplicity

When I first started designing algorithms to trade stocks, I was fascinated with all things related to artifical intelligence and truly believed that this was the way forward in developing an 'all seeing' formula for success.

My early attempts at algo-trading were notable for the complexity of the models I built and, in due course, the realisation that I was merely adjusting variables and parameters in order to 'fit' my model more closely to a back-tested curve. It was only after reading Ernie Chan's great book Quantitative Trading did I realise that it is more important to work on simplifying one's model that trying to make it all singing and all dancing. Strangely, the hardest thing about model development is to simplify it. You know you are getting close to a good model when adding one more variable or parameter moves it away from the optimum. I have found through 'gut feel' rather than empirical evidence, that anymore than 4 or 5 variables and the same number of parameters makes a model less than optimal.

A simple model will allow it to work in more varied market environments, with more resilience and for a longer time. When you are reading about the complexity of the Wall Street quant models, always remember that simplicity is the friend of the amateur.

The Daily 'Scrape'

In the last post we talked about about how Google 'hides' its price data behind its own custom id's and how we can retrieve and match them to real EPICs for our own uses. In this post we will cover the actual exercise of scraping price data and the pitfalls to watch out for. A word of warning before we progress, the code listed below is not as well documented as I would like, however please ask if you have questions.

I download pricing data everyday for all companies in the FTSE100 and FTSE250. I also download 6 years-worth of data for each EPIC every day (750,000 records). You may ask why delete and re-download everything every day? The reason is, I believe there are errors in the daily data that are sometimes corrected by Google and I wouldn't catch these 'corrections' if I didn't download everyday. I may be very wrong in this opinion, but it makes me feel happier.

The actual 'work' of fetching the Google price data is covered by the following code:

   protected static DataTable DownLoadPricesFromGoogle(DateTime latestDate, 
                                      DateTime earliestDate, string googleCid)  
     {  
     Begin:  
       string urlTemplate =  
         @"http://finance.google.co.uk/finance/historical?cid=
                     [symbol]&startdate=[earliestMonth]%2F[earliestDay]%2F[earliestYear]
                      &enddate=[latestMonth]%2F[latestDay]%2F[latestYear]&output=csv";  
       string latestMonth = latestDate.Month.ToString("00");  
       string latestDay = latestDate.Day.ToString("00");  
       string latestYear = latestDate.Year.ToString("00").Substring(2, 2);  
       string earliestMonth = earliestDate.Month.ToString("00");  
       string earliestDay = earliestDate.Day.ToString("00");  
       string earliestYear = earliestDate.Year.ToString("00").Substring(2, 2);  
       urlTemplate = urlTemplate.Replace("[symbol]", googleCid);  
       urlTemplate = urlTemplate.Replace("[latestMonth]", latestMonth);  
       urlTemplate = urlTemplate.Replace("[latestDay]", latestDay);  
       urlTemplate = urlTemplate.Replace("[latestYear]", latestYear);  
       urlTemplate = urlTemplate.Replace("[earliestMonth]", earliestMonth);  
       urlTemplate = urlTemplate.Replace("[earliestDay]", earliestDay);  
       urlTemplate = urlTemplate.Replace("[earliestYear]", earliestYear);  
       string history;  
       var wc = new WebClient();  
       try  
       {  
         history = wc.DownloadString(urlTemplate);  
         Thread.Sleep(1000);  
       }  
       catch (WebException ex)  
       {  
         if (ex.Status == WebExceptionStatus.ProtocolError)  
         {  
           return null;  
         }  
         Thread.Sleep(3000);  
         goto Begin;  
       }  
       finally  
       {  
         wc.Dispose();  
       }  
       return Parse(history, true);  
     }  

The 'Begin:' at the top of the code is a horrible 'kludge' put in to get around any delay in fetching the page - I have as yet not found a better way. The rest of the code is fairly self explanatory.

The 'Parse' method converts the document stream into a datatable - to be honest I should get around to rewriting this as there is really no need for this and a much simpler method such as a simple data transport object would suffice:

  public static DataTable Parse(TextReader stream, bool headers)  
     {  
       var table = new DataTable();  
       table.TableNewRow += Table_NewRow;  
       var csv = new CsvStream(stream);  
       string[] row = csv.GetNextRow();  
       if (row == null)  
         return null;  
       if (headers)  
       {  
         foreach (string header in row)  
         {  
           if (!string.IsNullOrEmpty(header) && !table.Columns.Contains(header))  
             if (header.Contains("Date"))  
             {  
               table.Columns.Add("Date", typeof(string));  
             }  
             else  
             {  
               table.Columns.Add(header, typeof(string));  
             }  
           else  
             table.Columns.Add(GetNextColumnHeader(table), typeof(string));  
         }  
         row = csv.GetNextRow();  
       }  
       while (row != null)  
       {  
         while (row.Length > table.Columns.Count)  
           table.Columns.Add(GetNextColumnHeader(table), typeof(string));  
         if (String.IsNullOrEmpty(row[1]))  
         {  
           Debug.WriteLine("`");  
         }  
         table.Rows.Add(row);  
         row = csv.GetNextRow();  
       }  
       return table;  
     }  
     private static void Table_NewRow(object sender, DataTableNewRowEventArgs e)  
     {  
       Console.WriteLine("Table_NewRow Event: RowState={0}",  
                e.Row.RowState);  
     }  
     private static string GetNextColumnHeader(DataTable table)  
     {  
       int c = 1;  
       while (true)  
       {  
         string h = "Column" + c++;  
         if (!table.Columns.Contains(h))  
           return h;  
       }  
     }  

The 'RefreshPriceData' method controls the program flow of the 'scrape' - key points to note are:

• the 'Persistence' class is purely a utility class to clean-up the backend database prior to the receiving of a new set of data. To be honest, with the advent of Linq-to-Sql, I could probably get rid of 'Persistence' and keep everything in line.
  
  
• The code checks for the presence of 'GoogleEpicCodes.xml' in the path - as previously mentioned, this file is the 'key' for matching Google id's to real EPICs. If the file doesn't exist, it is created in the 'FetchGoogleCIDs' method.
• I use a store of known EPICs in my database to filter what prices I am going after - these EPICs are checked against the data from 'GoogleEpicCodes.xml' to ensure validity and to filter for only FTSE100 and FTSE250 companies - the LINQ code to fetch the EPICs from the SQL database is:
  

  IQueryable<string> epics = (from e in _db.StatsLists  
                       where e.FTSE_Index == "FTSE100" || e.FTSE_Index == "FTSE250"  
                       orderby e.EPIC  
                       select e.EPIC);

  
  
•  An interesting piece of code surrounds the line:
  

   if (!_dt.Rows.Contains(keysFind))

  The reason for this is that I have found occaisionally that Google will throw the same price/date pair in twice - I don't know if this is designed to confound scrapers, but this small check before adding the record will save you much grief later.
  

 public void RefreshPriceData()  
     {  
       _db = new DataClassesDataContext();  
       _db.Connection.ConnectionString = Database.ConnectionString;  
       //Delete all prices first  
       Persistence.ExecuteDelete();  
       string path = Environment.CurrentDirectory;  
       path = string.Format("{0}\\", path);  
       if (!File.Exists(path + "GoogleEpicCodes.xml"))  
       {  
         FetchGoogleCIDs();  
       }  
       DataTable gId = GetGoogleCids(path + "GoogleEpicCodes.xml");  
       IQueryable<string> epics = (from e in _db.StatsLists  
                     where e.FTSE_Index == "FTSE100" || e.FTSE_Index == "FTSE250"  
                     orderby e.EPIC  
                     select e.EPIC);  
       DataTable _dt = Persistence.CreateDataTable();  
       var keys = new DataColumn[2];  
       keys[0] = _dt.Columns["EPIC"];  
       keys[1] = _dt.Columns["Date"];  
       int count = 0;  
       foreach (DataRow row in gId.Rows)  
       {  
         if (epics.Contains(row[0]))  
         {  
           Console.WriteLine("Starting on " + row[0]);  
           DataTable tempdt = DownLoadPricesFromGoogle(row[1].ToString());  
           if (tempdt == null)  
           {  
             continue;  
           }  
           var keysFind = new object[2];  
           foreach (DataRow dataRow in tempdt.Rows)  
           {  
             DataRow newRow = _dt.NewRow();  
             newRow["EPIC"] = row[0];  
             newRow["Date"] = 
                    DateTime.ParseExact(dataRow["Date"].ToString(), "d-MMM-yy", null);  
             newRow["Open"] = Convert.ToDecimal(dataRow["Open"].ToString());  
             newRow["Volume"] = Convert.ToDecimal(dataRow["Volume"].ToString());  
             decimal high = Convert.ToDecimal(dataRow["High"].ToString());  
             decimal low = Convert.ToDecimal(dataRow["Low"].ToString());  
             decimal close = Convert.ToDecimal(dataRow["Close"].ToString());  
             newRow["High"] = high;  
             newRow["Low"] = low;  
             newRow["Close"] = close;  
             keysFind[0] = row[0];  
             keysFind[1] = 
                      DateTime.ParseExact(dataRow["Date"].ToString(), "d-MMM-yy", null);  
             if (!_dt.Rows.Contains(keysFind))  
             {  
               _dt.Rows.Add(newRow);  
             }  
           }  
           if (count % 30 == 0 && count != 0)  
           {  
             Persistence.CopyData(_dt);  
             _dt.Rows.Clear();  
           }  
           count++;  
         }  
         Persistence.CopyData(_dt);  
         _dt.Rows.Clear();  
       }  
     }  

Implementation of this code will allow you to easily 'scrape' the data you need. I run this every night prior to testing, each run netting some three-quarters of a million records within the space of about 15 minutes.

Next time we will discuss how to easily insert so many records into an SQL database.

Getting Your Data: The Prerequisites

Scraping Data

(A word of warning before we start. The legalities or otherwise of data 'scraping' must be highlighted. Most providers do not like you doing this - as I am an 'amateur' I feel I have a little leeway in this stricture, however you have been warned!)

As a budding algo trader, your initial problem is getting data to test. There are sources of commercially available end-of-day data however these tend to be expensive and seldom in a form I like. I originally used to 'scrape' data from Yahoo, however I found the data was erratic and often 'changed'. Additionally, the Yahoo data appeared to be patchy and I was never truly sure it was any good. Eventually I moved over to using Google's own end-of-day data. I have found this to be very good and consistent as well as having suitable history.

There is a difficulty with Google data that is not apparent on Yahoo's - Google does not make it easy for us to automate downloading of stock prices as each URL is addressed using Google's own code for the stock e.g. Vodaphone's EPIC (exchange symbol) is 'VOD' whilst Google uses the code '834331'.

The code snippet below will fetch Google id's for each each EPIC you supply - in this snippet, the EPICs are fetched into a <string> list using the function:

EpicList.GetEpicListFromYahoo();

This could as easily be a Linq-to-Sql call from a database. You can quite easily get the list of EPICs from Yahoo to populate your table beforehand.

The FetchGoogleCIDs method eventual saves the resultant data to an XML file named 'GoogleEpicCodes.xml' - this was just done for ease of use.

 public static void FetchGoogleCIDs()  
     {  
       IList<string> epiclist = EpicList.GetEpicListFromYahoo();  
       var dt = new DataTable("Epics");  
       dt.Columns.Add("EPIC", Type.GetType("System.String"));  
       dt.Columns.Add("GoogleId", Type.GetType("System.String"));  
       dt.AcceptChanges();  
       var wc = new WebClient(); //create new webclient for process  
       foreach (string s in epiclist)  
       {  
         Console.WriteLine("Starting on " + s);  
         string thisEpic = s.Replace(".L", "");  
         string urlTemplate = 
                 "http://finance.google.co.uk/finance/historical?q=LON:" + thisEpic;  
         string history = "";  
         try  
         {  
           history = wc.DownloadString(urlTemplate);  
         }  
         catch (WebException)  
         {  
           throw new WebException("Problem fetching GoogleId for " + thisEpic);  
         }  
         finally  
         {  
           wc.Dispose();  
         }  
         
         string cid = RegexCID(history);  
         if (!string.IsNullOrEmpty(cid))  
         {  
           DataRow newRow = dt.NewRow();  
           newRow["Epic"] = s.Replace(".L", "");  
           newRow["GoogleId"] = cid;  
           dt.Rows.Add(newRow);  
         }  
         else  
         {  
           Debug.WriteLine("");  
         }  
       }  
       wc.Dispose(); //Clean-up  
       string path = Environment.CurrentDirectory;  
       path = string.Format("{0}\\", path);  
       dt.WriteXml(path + "GoogleEpicCodes.xml", false);  
     }  

One method missing here is the method call:

 string cid = RegexCID(history);  

This uses a regex match to extract the relevant Google id for the stock as is listed below:

 private static string RegexCID(string html)  
     {  
       string regex = @"<input.type=hidden.name=cid.value=.(?<cid>.*).>";  
       RegexOptions options = ((RegexOptions.IgnorePatternWhitespace 
                   | RegexOptions.Multiline)  
                   | RegexOptions.IgnoreCase);  
       var reg = new Regex(regex, options);  
       MatchCollection matches = reg.Matches(html);  
       if (matches.Count == 0)  
       {  
         return string.Empty;  
       }  
       GroupCollection groups = matches[0].Groups;  
       string match = groups["cid"].Value;  
       return match;  
     }  

By iterating through your list of EPICs and applying the above methods, you will quickly derive a list of the associated Google id's required for the main price scraping exercise.

Be warned that Yahoo's list of EPICs is horribly mangled as Yahoo insists of attaching '.L' after each one - in the process stocks such as 'BT.A' (British Telecom) become 'BT-A.L'. This can be a source of major problems later on. I actually get my list of EPICs from sharelockholmes.com who luckily do not mess them around.

My next post will concentrate more on the scraping engine and the utility classes surrounding it.