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

Source: www.nasdaq.com

WebClient response and data caching

Below is the synchronous static method that returns our historical dividends.

// 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);
		}

// 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.

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);
	}
}

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:

HTML Markup

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:

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);
}

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"))