This post is about how I create and publish the UK Dividends Champion List and I thought I’d add some details on how my new site works while I was at it.
Here are some stats about the data behind the List so far…I was going to call them fun-filled facts but I think that’d be a stretch!
|Companies in my main working list||1,609|
|Companies with 4+ Years Dividend Growth||269|
|Manually researched companies||138|
|Dividend payment records||5,179|
|Stock splits processed||92|
|Company annual reports downloaded||1,224|
I maintain a master list with about 1,600 companies in it which are the companies in the AIM and FTSE indexes I could find. Of these, the majority haven’t increased dividends for 4+ years and I currently track 269 which have done so. This is the subset of companies that I include in the UK Dividend Champions List.
The Research Effort
When I originally compiled the list, I based it heavily on dividend history and yield data from Yahoo which, if you’ve read some of my blog comments, is frequently wrong. So I’m in the process of reviewing and correcting the data but it’s a fairly slow process since it’s all manual. I’ve done about 138 companies and processed 5,179 dividend payments to date. It takes me anywhere from 20-60 minutes per company depending on how complete / accurate the online dividend data is. The calculations include 92 stock splits from various companies that I’ve managed to find too.
And one other thing I do is to download company annual / interim reports as I go as that’s a prime source of information I use to confirm dividend history. I’m all OCD like that. Eventually I’d like to post links to the reports since the older ones can be hard to find.
Publishing the UK Dividend Champions List
So the following diagram, in a tasteful red color, shows the steps and tools involved in updating the list.
1. Update from manual research
Everything starts with entering data into my Access database. This is still being built up and it contains the dividend history and details of the 138 researched companies. I actually have two database files – a “front-end” and a “back-end”, apparently this is ‘good practice’ in database organization. It really just means that the back-end file is simply data and nothing more. The front-end file contains queries, reports and forms that are populated with the data from the back-end file. Keeping the two separate means that I could move the back-end database to another server or something if I wanted to, so it’s more flexible. I’m not planning to do that and I’ve not taken the time to learn how to create data entry forms or reports yet.
So anyway, I look at the dividend data on Yahoo, check it with data from sites such as www.investorease.com and/or Morningstar, find the annual reports that I can and check stock splits. All of this data gets entered into the database, along with additional information about the company that I collect such as when it was founded or notes about significant events.
2. Update company data
Because the database isn’t complete, I have my main ‘working’ spreadsheet with all of the 1,600 companies in it. The latest data from the database is imported with a Visual Basic script and merged, so that any wrong data in the spreadsheet for the stocks I’ve researched is corrected. The only thing missing at this point is any updated dividend history for the non-manual companies and, when I come to publish the list at the month’s end, the fundamental data for stock price, P/E etc.
3. Import prices
Another set of scripts gets the fundamental data from public sources on the internet. It’s mostly Price and EPS that I need at this point, and the scripts take a while to run so I’m glad to only have to do it once a month! If the List ever makes a profit then I’d consider paying for this data but I don’t really see that happening.
4. Publish List
Once the price / EPS information is imported allowing the yield, P/E, Payout Ratios to be calculated then it’s simply a mouse click to publish a new report for the month end with yet another script which exports the data from the Working list. To save time I wrote another script to compare the old / new Reports and write the revision history; most of the time at this stage is spent with a sanity check of the file to make sure that the identified changes are intentional.
The list is stored in “the Cloud” using an online storage account – if you look at the URL for the list you’ll see that it’s http://files.dividendchampions.uk. I’ll explain this in the next section.
5. Generate Website Data
This step is fairly new and I added it to generate the data used by DividendChampions.UK. Originally the early versions of the website used an online database that I was populating with data from my local database, but for reasons I’ll get to shortly I’m moving away from that. So this step now generates a set of XML data which is uploaded to “the cloud”.
6. Website reads XML
So the last step is simply for the website to load the XML data and show the company information. I’ll be adding the dividend history data in the near-term; tonight’s update to the site included the dividend split history for each company. Typically Yahoo is quite accurate for this data, but it’s helpful to display it especially since it affects the dividend growth calculations. Please see my earlier post on how dividend splits affect dividend growth.
So building a website from scratch is a much different exercise from creating a blog such as WordPress. Managing a blog is a good initial step if you have no experience and want to create a website, since so much is done for you.
There are many separate technologies involved in website design and I won’t cover them all here. Here are some key languages used:
|Language||What it’s used for|
|HTML||Web page layout|
|CSS||Web page presentation and formatting|
|PHP||Server-side scripting language for web-page generation|
|SQL / MySQL||Two different dialects of Database query languages|
|ASP.net||Server-side programming using Microsoft’s .NET language|
|C#||Microsoft’s interpreted language similar in concept to Java|
|XML||Data definition language that describes data allowing it to be easily manipulated|
|JSON||Another way of defining data using a different notation than XML|
And this is really just scratching the surface since there are many other alternatives and languages that can be used.
Client vs. Server Scripting
So in the old-days when there wasn’t any CSS and animation, web sites were very simple. You pointed your browser (the Client) at a website and the browser asked the web-server (the Server) for a page. You then clicked on another link which made the browser client ask the Server for the new page, or you entered some text on a form and pressed Submit which made the browser post the information to the Web Server. Either way, some scripts could run in the web-server based on the data it received from the browser and it could dynamically generate the HTML page that you saw next. This is the essence of “Server-Side” scripting…it’s code which runs on the web-server and it’s used to generate the Web pages that you see as well as interact with databases or external services to make things happen such as handle a credit card payment.
Client-side scripting is code which runs on your Browser, not on the Web-Server. So suppose the web-server sent you a list of 1,000 items; client-side scripting allows you to filter the list based on button presses without needing any additional commands to the web-server. Or it allows you to show a message if data is invalid without needing to ask the web-server.
There are some things you can do either using client-side or server-side scripting, so there’s usually different ways of achieving the same goals. But you can’t avoid server-side scripting if you’re creating a web-site, whereas you can avoid client-side scripting if you have a simple HTML user experience.
What I chose
I decided to stick with Microsoft’s ASP.NET technology for creating DividendChampions.UK. I already know the C# language and own Visual Studio 2013 Professional. I have enough work in learning how to use CSS and HTML5 in anger without adding PHP and MySQL on top. Plus I’m familiar with the Visual Studio development environment and debugger which is a great tool. Microsoft have been much more open in the last few years, so it’s easier to integrate other languages and solutions into their framework if I chose to do so later. So this was really a case of minimizing the number of new things for me and leveraging skills that I already have to save time.
To run a website you need a Domain Name and a Hosting Account. You usually get these from the same company, but they’re entirely separate things really. For Hosting there are usually several different options along the following lines.
With shared hosting you’ll get a virtual web-server with access to a control panel where you can configure it plus an ftp-site where you can upload files to the web-server. This is the cheapest method of running a website; the accounts are typically limited by bandwidth, disk-space, number of databases and CPU / Memory. Most companies allow you to scale the web-server to higher performance by paying more, but in general your web-server runs on a real server along with other people’s web-server.
If you were hosting a blog, then typically one of the Control Panel options will be “Install WordPress” and that button will take care of installing WordPress and setting up the database for you to use. You can even manage multiple websites from a single shared hosting instance, but you’ll need multiple domain names in this case.
With cloud hosting you’re allocated a virtual web-server that runs in the cloud. This is more expensive than shared hosting and means that you’re less affected by a physical server failing since the virtual web-server can be more easily managed in the cloud and moved from server to server. Otherwise it’s similar to Shared hosting; you don’t get real access to a real server but you have a control panel to let you configure things.
Virtual machines is another option where you’re given control over a virtual server and you can install whatever software you like on it. Your interface to the server is via Remote Desktop or command line, and if you want a web-server you have to install it yourself. You can request more memory or CPU as needed and this solution gives you much more flexibility and control than the other options.
I decided to avoid a hosting company altogether as I’ve had quite bad experiences so far with my current blog hosting with a number of lengthy outages. You get what you pay for I suppose since it’s cheap! 😉
Anyway, DividendChampions.UK is hosted on Microsoft’s Azure Cloud service. They pronounce the name funny; apparently it’s Azure as in Razor, not Azure as in Allure which is how I grew up with the word. But either way, it’s all pretty cool although more expensive than a simple hosting account.
I added a Storage account too for the published Excel files – the first 5GB is free, and it’s a simple matter to point a sub-domain (e.g. files.dividendchampions.UK) to the virtual IP that the storage account provides. The files get automatically stored in three different drives in the Azure datacenter so they’re pretty safe, and you can control the public vs. private access. The Excel files are public, the website data files are private.
On the plus side with Azure, I can create any number of new websites with a public-facing domain name such as “mysite.azurewebsites.net” and it’s a pay-as-you-go system, so if you create a web site or virtual machine and turn it off, there’s no charge until it’s turned back on. But you’re charged while it’s running and it doesn’t matter if it’s doing nothing in that case. They do offer a “free” service plan which is for trial / evaluations; you get 1 hour of CPU time a day, so for a really low volume site it’s okay since the CPU is usually working for milliseconds at a time. But if there’s no traffic then they’ll shut the site down so it’ll take a few seconds to start back up when you access it again.
If the site ever becomes really popular I can double the memory / performance with a click, and conversely if there’s no traffic I can scale it back down to reduce costs. There is one great feature with the current plan where I can deploy a new version of the website to a development slot and test it with a public URL. If everything works as expected, it’s a simple one-button press to swap the development site with the production site and it means no downtime for the site. So I can patch and do updates without anyone being impacted which is really cool.
Is a database necessary?
I mentioned earlier that I was moving away from using a database on my website; it’s actually still running today and it’s used to build the CY / FY list pages whereas the Company Sector pages are done from XML. It’s a pain to manage the online database while I’m still designing the database schema and keeping track of the database migration rules is painful. I already managed to delete the database once too although Azure lets me restore deleted databases which was handy. Plus the tools to manage the database online are quite slow and clunky and not as convenient as Access on my local PC. Since I don’t need to do a lot of real-time calculations on the data, I’m just generating the calculated files in XML and saving them in the Cloud where the Web site running in the Cloud can easily read them and display the appropriate pages.
It’s also quite expensive to add a Database in Azure compared to standard Storage – a minimum of $5 a month which I can avoid with using standard Storage.
Anyway, that’s probably enough from me for now. Congratulations if you made it this far through the post; it turned out a little longer than expected!
Quote of the day
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.