C# Historical Dividend retrieval
Today in SmileOfThales we will provide you some brief but useful C# code to retrieve historical cash dividend data in Excel (the whole code is available at the end of the article ).
The topic covers Excel-Dna, data caching, Html parsing with HtmlAgilityPack … that’s it and it’s already pretty 🙂
At the very beginning I needed to retrieve dividend history to experiment implicit dividend solving: implicit the future dividend ex-div dates from the previous ex-div dates (assuming a regular schedule), and use the latest cash dividend as a starting point for my solver.
Now since I work a lot in Excel, I could copy paste it manually from the web page into Excel but it revealed to be painful, and I wanted to do it for several tickers, so … I finally decided to retrieve directly this data via Excel-Dna, which offers some nice .Net integration into Excel, with easy asynchrony.
Some websites, but only a few, offer free dividend time series: http://www.dividend.com/, http://www.nasdaq.com/ … I opted for Nasdaq.com whose layout focuses on the essential information.
Ex for Caterpillar, the dividend history can be obtained at http://www.nasdaq.com/fr/symbol/cat/dividend-history
WebClient response and data caching
Below is the synchronous static method that returns our historical dividends.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
// synchronous method private static object NasdaqDividend(string ticker, bool useCache) { try { var url = string.Format(Url, ticker); var response = new HtmlDocument(); string respString; try { if (useCache) // Get data from cache if already retrieved using (var reader = new StreamReader(string.Format(CacheFormat, ticker))) { respString = reader.ReadToEnd(); } else { LoadAndSave(url, ticker, out respString); } } catch (Exception) { LoadAndSave(url, ticker, out respString); } |
We use the following method in order to call Nasdaq website (the url format is http://www.nasdaq.com/en/symbol/{0}/dividend-history) and store it in a local textfile.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
private static void LoadAndSave(string url, string ticker, out string respString) { // Download data ... using (var web = new WebClient()) { respString = web.DownloadString(url); } // And store it using (var writer = new StreamWriter(string.Format(CacheFormat, ticker))) { writer.Write(respString); } } |
We make the choice to have a single storage file per ticker, and user has to force retrieval in order to make a call to the server (otherwise dividends are taken from the cache).
Html parsing with HtmlAgilityPack
The Nasdaq website returns a long Html response, but at the very end we can catch the table markup of our consolidated coupon history:
In order to parse the elements containing each dividend detail, I used a .Net library called HtmlAgilityPack that makes it easier to parse such Html document, which reveals trickier than deserializing a JSON response.
This is the section of code related to Html parsing:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
response.LoadHtml(respString); var apprtr = response.DocumentNode.Descendants("tr").Where (x => x.InnerHtml.Contains("quotes_content_left_dividendhistoryGrid")).ToList(); IFormatProvider culture = new CultureInfo("en-US"); var dividends = new List<Dividend>(); foreach (HtmlNode htmlNode in apprtr) { DateTime exDate; DateTime recDate; DateTime payDate; double cashAmount; DateTime declDate; var exDivStr = htmlNode.Descendants("td"). FirstOrDefault(p => p.InnerHtml.Contains("quotes_content_left_dividendhistoryGrid_exdate")) .Descendants("span").FirstOrDefault().InnerText; var cashAmtStr = htmlNode.Descendants("td"). FirstOrDefault(p => p.InnerHtml.Contains("quotes_content_left_dividendhistoryGrid_CashAmount")) .Descendants("span").FirstOrDefault().InnerText; var declDateStr = htmlNode.Descendants("td"). FirstOrDefault(p => p.InnerHtml.Contains("quotes_content_left_dividendhistoryGrid_DeclDate")) .Descendants("span").FirstOrDefault().InnerText; var recDateStr = htmlNode.Descendants("td"). FirstOrDefault(p => p.InnerHtml.Contains("quotes_content_left_dividendhistoryGrid_RecDate")) .Descendants("span").FirstOrDefault().InnerText; var payDateStr = htmlNode.Descendants("td"). FirstOrDefault(p => p.InnerHtml.Contains("quotes_content_left_dividendhistoryGrid_PayDate")) .Descendants("span").FirstOrDefault().InnerText; DateTime.TryParse(exDivStr, culture, DateTimeStyles.AdjustToUniversal, out exDate); DateTime.TryParse(recDateStr, culture, DateTimeStyles.AdjustToUniversal, out recDate); DateTime.TryParse(payDateStr, culture, DateTimeStyles.AdjustToUniversal, out payDate); double.TryParse(cashAmtStr, out cashAmount); DateTime.TryParse(declDateStr, culture, DateTimeStyles.AdjustToUniversal, out declDate); var div = new Dividend { CashAmount = cashAmount, DeclDate = declDate, RecDate = recDate, PayDate = payDate, ExDiv = exDate }; dividends.Add(div); } |
Excel asynchronous call
As it’s always unpleasant to freeze Excel for a long calculation (here data retrieval), we expose an asynchronous in Excel, instead of the previous one:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
// Asynchronous method [ExcelFunction(Name = "NasdaqDividendHistory", Description = "Get dividend history for a particular ticker", Category = "Nasdaq Data", IsVolatile = false)] public static object NasdaqDividendAsync(string ticker, bool useCache) { try { return ExcelAsyncUtil.Run("NasdaqDividendAsync", new object[] { ticker, useCache }, () => NasdaqDividend(ticker, useCache)); } catch (Exception ex) { return ex.Message; } } |
Here is the shape of our cash dividend time series for CAT:
In a future article we will show you how you can retrieve option quotes from Google Finance and export it to Excel.
Leave a Reply