How to get Free Intraday Stock Data from Netfonds

/

Daily stock data is everywhere for free. Yahoo, Google, and Quandl all provide useful daily stock prices for basic number crunching. However computational analysis for intraday stock data is much harder to find. In fact, Intraday stock data can be very expensive. So what is a cost conscious quant supposed to do?

The Norwegian website Netfonds.no provides free intraday data on stocks and ETF's on the NYSE, Nasdaq, and Amex exchanges. They provide up to 5 days of trade/bid/offer data. I wrote some code to grab that data easily and quickly that I will share with the Python community.

Before I paste the code below let me give a h/t to Yves Hilpisch who wrote the excellent 'Python for Finance' book where I borrowed some of the following code. 

I've embedded the entire script using Nike ( NKE ). Feel free to ask questions and/or add your own touches. 

 

Here are the resulting sample plots.


How I use Implied Cost of Capital (ICC) as a market valuation tool

/

What is Implied Cost of Capital?

“In accounting and finance the implied cost of equity capital (ICC)—defined as the internal rate of return that equates the current stock price to discounted expected future dividends—is an increasingly popular class of proxies for the expected rate of equity returns. ”
— CHARLES C. Y. WANG; an assistant professor of business administration in the Accounting and Management Unit at Harvard Business School

I love the intuition behind the model although I don't use it as proxy for expected returns. I use it as a relative value measure to identify analyst/institutional sentiment between different market sectors at a point in time. 

The actual calculation of the measure can be somewhat complex and involved. The below equation is the common form of the ICC model.

As an active trader my primary concern is practical application and implementation so I simplified and streamlined the calculation as much as possible. 

I calculate the implied cost of capital for S&P SPDR ETF's representing a diverse cross section of sectors and industries. I calculate a simplified version by using the following process:

  1. I calculate an implied book value of equity per share, by taking the most recent ETF closing price and dividing by the given Price to Book ratio.
  2. I calculate an implied 1 yr forecast estimate of EPS by dividing the last ETF closing price by the given 1 yr forward P/E ratio.
  3. For the sake of brevity I then make a gross assumption by setting the current implied BV of equity equal to last year's BV of equity.
  4. I then use the median ETF price over the most current month, and assume a long term growth (g) of 5% for use in calculating a terminal value. 
  5. I then estimate the ICC using 2 methods.
    • I use the formula shown above and solve for 'R' which is the ICC estimate
    • I simplify the above equation into a simple capital budget style IRR function. I use a negative current median price as the initial cash outflow, assume a holding period of 1 year, and then I assume at the end of that 1 year holding period we are able to sell our stock for the price we paid plus next year's estimated earnings per share.

Again I make a LOT of dubious assumptions for the sake of simplicity, but to reiterate I'm using the metric as a relative value indicator and NOT a proxy for expected returns. My goal in looking at the various sectors is to try and identify which areas of the market are priced at relative extremes (discounts/premiums) compared to analyst forecasts' and current market sentiment. 

The 'sanity check' if you will can be found by looking at the extremes. You would expect the ETF's with the lowest ICC to have had relatively large appreciation of price relative to EPS forecasts. For example ( XBI ) the biotech ETF, ( XHE ) health care equipment ETF, and ( XPH ) the pharmaceutical ETF have seen their share prices advance significantly over the past several months. At the other end of the spectrum you would expect the opposite and for the most part you see that too. ( XME ) the metals and mining ETF, ( XES ) the oil and gas equipment & services ETF have seen their share prices crushed. And as a barometer of the market overall you would expect to see ( SPY ) somewhere in the middle perhaps towards the lower end of ICC estimates due to the continued appreciation of the US stock market overall. Guess what? If you examine the data you find ( SPY ) where we would expect it, modestly priced at 13.5% somewhere in the lower middle range.

Where the metric is interesting is in the edge cases. For example ( KIE ) the insurance ETF has a relatively large ICC measure. However, a quick glance at the chart shows a relatively volatile range with a clear positive trend channel. So what gives? That implies the sector overall may be undervalued as EPS forecasts are either unchanged or still relatively optimistic in comparison to the modest stock gains of the ETF.  If you are fundamental investor/value investor the relative ICC can give you insight into where you should focus your search. 

I'm considering posting this metric either weekly or bi-weekly. I'll think on it some more. Until then, feel free to comment, question or provide feedback. 

Why I bought back into RDC ( UPDATED 3.15.2015 )

/

I'm going to keep this relatively short and sweet. 

Back in late December 2014 I bought ( RDC )  at a cost basis of ~$20 for the following reasons:

  • The bulk of RDC's most critical assets are contracted through 2015 and into 2016
  • Strongest balance sheet (lowest debt leverage) compared to other offshore drillers in the industry
  • Management team considered best in industry
  • Long term buyers appeared to defend the $20 share price level
  • At ~$20 the price to book is an absurd 0.5! Simply put you can purchase a $1 worth of assets of for $0.50!

I was able to take profits at ~$23 and ~$22 for an average gain of ~12.5% on the position. Not bad considering the highest the stock price reached was ~$24, before February's run up to $25. 

Fast forward to today. I bought back in at approximately $20.66 with a stop around $19.25 and a price target of ~$24. All of the above reasons still apply plus the following:

  • RDC took ~$430 million in asset writedowns for its 12 oldest rigs. As a result of the impairment charge they reported a loss in Q4 earnings. Therefore the new price reflects this new negative information.
  • Additionally ( HERO ), another offshore oil driller, recently had a long term contract cancelled by Saudi Aramco. All offshore companies and their investors were put on notice that contracts can and will be cancelled if the day rate is too high, and as a result offshore drillers' share prices tanked. Again the current share price reflects this added revenue uncertainty. 
  • The stock price has once again held the $20 level. 

Considering the aforementioned factors I believe this is a solid entry based on the risk/reward setup.

Remember as traders/investors our only goal is to develop the skills to identify and exploit asymmetries in the market. We will never be right 100% of the time, but with proper due diligence, risk management, and belief in the process we can certainly be profitable over the long term. 

UPDATE: Price action was terrible in the week that followed this post, with a violent break towards $19 violating my stop price. With recent reports of still record supply coming online, ( USD ) strength, the price of oil and market sentiment is likely to be suppressed for some time. ( RDC ) is also exposed to concentration risk per the following Seekingalpha report:

“The analyst also sees Saudi Aramco continuing to cause problems for drillers, particularly Rowan (NYSE:RDC), whose exposure to Saudi Aramco is 34.4% of its ~$5.1B backlog, and a potential discount could negatively impact RDC’s gross EPS by $0.45-$0.55/year; ”
— Mar 11 2015, 15:48 ET | By: Carl Surran, SA News Editor

The lesson is stay disciplined with your trades and mentally flexible. Always be looking for new relevant information that could affect your trades/positions.