Data Explorer and Power View to analyse Stock Market Insider Trades


Boardmembers of market-listed corporations are liable to publish their share transactions. You can lookup these announcements at msn.com-> http://investing.money.msn.com/investments/insider-transactions?symbol=MSFT 

Yet it was quite difficult to extract all the data directly from the website. Now with the new Data Explorer it just takes a few minutes to download all the information to excel and do exciting analysis.

Here is an example: The following chart shows the Volume of Stocks sold and purchased across all S&P 500 industries by CEOs and Hedge funds in the US in the last 2 years.

 If we drill down to the Information Technology Sector we see that most CEOs sold their stocks. There are just a few exceptions like Netflix.

 

An insider named HOAG JAY C purchased Netflix for a price of 72$ in May 2012. Today the price per share for Netflix is 169$. Makes a plus of around 97$ per share or 233%.

The same insider sold Netflix for 164$ in January 2013 realizing a profit of 31.6 Million USD - Great investment decision! 

In the next chart we compare the number of right to the number of wrong trade decisions. Selling a stock where the current price is lower or buying a stock where the price is higher today is considered as a right decision. Selling stocks that have a higher price today or buying stocks that have a lower current price is counted as a bad decision. The first barchart shows weighted decisions by volume, the second chart is just the plain number of right vs wrong decisions.

 

You can also drill down and find out in which industry our insiders are better informed (or just had more luck).

Another perspective is the Average rate of Return by Industry:

Following the insider`s buy decisions would have been a good idea in almost any sector except for Materials. Buying Aluminium or Steel would have caused a loss of up to 25%.

How can we build this reporting model now?

First we need a list of stock symbols. We can use the Data Explorer to search for "S&P 500". The first entry should return the list.

The next step is the most difficult one. For each Stock Symbol we call the MSN Website providing the Symbol as a parameter. Make sure that the advanced scripting option in the Data Explorer Settings is enabled. When you click the Filter & Shape Button next to your downloaded list you can edit the script code:

 

Write the following statement in the "let" area:

InsertedCustom = Table.AddColumn(Source, “Custom”, each Web.Page(Web.Contents(“http://investing.money.msn.com/investments/insider-transactions?view=All“, [Query=[symbol=[Ticker_symbol]]] )))

and change the "in" to InsertedCustom:

Hint: at the beginning it can be good to work with a subset of records. e.g a Filter for Stocks in "Biotechnology Industry" should return just 5 records. This makes testing the script faster and fluid. It`s not always required but as this is still the Beta Version of the Data Explorer it ensures that you get a result quickly.

Now expand the table in the custom Column

and expand the Custom Data Column again:

The final step is to filter the Data. We are just interested in Buy and Sell Information.

You can use the GUI to create a filter by right clicking the cell and specifying that you are just interested in values equal to "Sold". After that you can manually copy the condition, change the value to "Purchase" and place an "or" inbetween:

Done! You can now remove the Filter and download everything. Be patient. It takes up to 20 minutes.

To analyse the performance of our trades we have to get current stock prices. Moreover we have to define Calculated Columns and Measures using DAX. Last but not least a Hierarchy is needed to make the drilldown work.

This is already done in the attached excel sheet. I will describe the steps to build the report model in my next blog post. Stay tuned!  

Directors Dealings Final.xlsx


Comments (0)

Skip to main content