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. 

Swiss FX Shock

/

On January 15, 2015 the Swiss National Bank (SNB) shocked the world with a surprise break away from the Euro peg which caused a cataclysmic revaluation of the relative value of the Euro and Polish Zloty.

Some context: During the summer/fall of 2011, in response to the European debt crisis the SNB stunned FX traders ~2.5 years ago when it announced it would peg it's currency to the Euro in an attempt to neutralize the Swiss Franc's (CHF) rapid appreciation due to its safe haven status. The SNB did this to protect the Swiss local economy which is 70% based on exports according to The Economist. Clearly, the SNB is no stranger to catching the market off guard.

As a trader I'm always interested in the circumstances surrounding these major announcements. Let's take a look at some of the relevant headlines from the 2011 European Debt Crisis.

Portugal’s $111 Billion Bailout Approved as EU Prods Greece to Sell Assets - May 16, 2011 - Bloomberg.com

Agency Cuts Greece’s Debt Rating Again -June 13, 2011- Nytimes.com

Greece set to default on massive debt burden, European leaders concede -Monday 11 July 2011 - theguardian.com

Debt Contagion Threatens Italy -July 11, 2011 - Nytimes.com

Global Finance Leaders Pledge Bold Action to Calm Markets - August 7, 2011 - Nytimes.com

Swiss bid to peg 'safe haven' franc to the euro stuns currency traders - Tuesday 6 September 2011 - theguardian.com

5 Central Banks Move to Supply Cash to Europe - September 15, 2011 - Nytimes.com

Clearly these were some hectic times in 2011. The headline risk was high as bad debt blowups lurked around every corner and Greece's default was all about assured. Let's look at how various markets were holding up then vs now. 

During 2011

In 2011 you can see ( TLT ) marching higher and higher all year, even after the surprise SNB peg to the Euro ~ 9/6/2011. Furthermore you can see the extreme appreciation of the ( CHF ) reaching ~30% in August basically forcing the SNB to act. Also of note, ( SPY ) tanked in late July, and increased volatility followed through November.

Current - 2015

Examining 1 year to today, we can see that ( TLT ) is similarly strong and gaining as headline risk increases. Of particular interest is the low relative volatility in ( SPY ) compared to 2011. Note however there was a market selloff prior to the current surprise SNB announcement. Look how big that   ( CHF ) revaluation is. 

SPX, FXF (CHF) daily log returns since 2010

Just to put the move in perspective the above chart shows the returns of the ( SPX, FXF ) since 2010. Talk about an outlier... 

The Bureau for International Settlements (BIS) triennial survey in April 2013 estimated that average daily turnover in the currency markets had reached $5.3 TRILLION! Factoring the size and breadth of the FX markets and the inherent leverage involved, a ~20% move in a major currency like the Swiss Franc is a major change in market dynamics that has wide reaching implications that aren't always immediately apparent.

Something like the Polish Zloty's ~20% decline vs the Swiss Franc could cascade into something bigger. According to Bloomberg News Poland has ~46% of total home mortgages denominated in ( CHF ) for a dollar value of ~$35 Billion. Imagine your monthly mortgage payment increased 20% overnight, would you be able to pay it? What would you have to give up to make good on your house note? This is the reality staring Polish citizens and banks in the face. My guess is a massive increase in bad debts, and asset writedowns are soon to follow. Accordingly, I'm not sure how much, if any of this is priced in equity markets with ( OIL ) dominating headlines and investor focus.

With that said I believe a bearish bias is prudent at this time as any 'unexpected' shock is likely to increase volatility in equity markets.  IMHO, the risk of the unexpected including: asset writedowns, profit warnings, bankruptcy's etc. likely resulting in margin calls, and forced selling; has increased dramatically. Be nimble, be adaptive, and manage your risk.

Is Canada a short?

/

In previous blog posts, I've discussed my bearish stance on the Canadian economy as expressed through ( EWC, FXC ) due to the collapse in global oil prices. My original hypothesis was Canada is a resource/energy based economy and declining oil prices would make the 'expensive' oil produced in the oil sands less attractive economically from a producer/investment standpoint. Let's explore that thesis a little more and see if it's validated by facts.   

Alberta, Canada contains the 'oil sands' deposits and had an estimated contribution of ~18% to Canadian GDP in 2013 - statcan.gc.ca via wikipedia

According to albertacanada.com, energy is ~23% of Alberta's GDP, and the ancillary sector of construction is ~11%. Back of the envelope calculations tell us that ~34% of Alberta's 18% Canadian GDP contribution is energy related. In other words ~6% of Canadian GDP is exposed to Alberta's energy sector.  

$1 per barrel drop in the price of Alberta's oil over 12 months = $215 million less in revenue - alberta.ca

In October of 2014 Bloomberg news reported that Alberta's budget forecast was based on oil prices at $97 a barrel. As of this writing the spot price of WTI crude is $51! That's a $46 gap, or ~47% drop. If crude averages ~$50 a barrel over 12 months that's an estimated ~$9.9B drop in provincial revenues. That will certainly blow holes in budgets and earnings expectations across the board. 

According to a RBC March 2014 provincial economic outlook Alberta was forecast to post the largest real GDP gains of any province for 2014 and 2015. I think that forecast will have to be altered drastically if it hasn't already. As of October 2014, BMO has already slashed Alberta's growth forecast to 2.9% from 3.3%. This might be revised further as the oil drop continues. 

How important is Alberta to Canada's economy? theglobeandmail.com reports:

“Alberta contributed one-third of Canada’s economic growth last year, and is by far the fastest-growing province in the country again this year. Since the beginning of 2013, nearly half the jobs created in the country were in Alberta.”
— http://www.theglobeandmail.com/report-on-business/industry-news/energy-and-resources/panic-time-as-oil-goes-so-does-canadas-economy/article21116012/

Civeo Corp. ( CVEO ) lost 53% of it's value on Dec. 29 after announcing a suspension of its dividend and closing sites - bloomberg.com

Why should you care? 

“Civeo Corporation provides workforce accommodations in the Canadian oil sands and the Australian natural resource regions. The company offers solutions for housing of workers with its long-term and temporary accommodations; and provides catering, facility management, water systems, and logistics services.”
— http://finviz.com/quote.ashx?t=cveo

( CVEO ) is on the front lines of Canadian oil sands production and announced it would cut staff in Canada by 30% and in the US by 45%. CapEX will drop ~78%! This is telling me that the fallout from the collapsing oil prices has only begun as capital budgets get cut across the board. 

Several oil majors have already cut or suspended major oil sands projects. Reportedly there is a tendency towards significant cost overruns for large scale oil sands production projects, that when paired with declining oil prices, make projects economically impractical. 

“The Joslyn oil sands mine has been shelved indefinitely, a result of rising industry costs that made the $11-billion project financially untenable.”
— http://www.theglobeandmail.com/report-on-business/joslyn/article18914681/
“CALGARY – Royal Dutch Shell PLC told regulators it is halting work on its Pierre River mine in northern Alberta’s oil sands and that it has no idea when it may revive the blueprints”
— http://business.financialpost.com/2014/02/12/shell-halts-work-on-pierre-river-oil-sands-mine-in-northern-alberta/?__lsa=09a5-a82e
“CALGARY — Statoil has put its Corner oil sands project on hold for at least three years as it grapples with rising costs.

The decision means about 70 jobs will be cut, the Norwegian energy firm said Thursday.”
— http://business.financialpost.com/2014/09/25/statoil-puts-corner-oil-sands-project-on-hold-for-at-least-three-years-cuts-70-jobs/

That's a pretty grim outlook moving forward, however there are risks to this thesis that must be noted and factored into any trade or investment decision. 

  • As oil price declines oil consuming provinces like Ontario stand to benefit
  • Oil prices in conjunction with a declining ( CAD ) make Canadian exports (including energy) cheaper for international buyers (primarily the U.S.), which is likely to offset some of the declining domestic economic activity
  • If Canadian exports rise enough to offset the domestic energy fallout   ( EWC ) may bottom more quickly than expected and even rise in the face of negative investor sentiment.
  • Canadian manufacturer's which use oil as an input also benefit from the declining oil price and may be in position to increase sales as consumers in other provinces and the U.S. have more discretionary cash to spend, again potentially causing ( EWC ) to bottom more quickly than expected and even rise in the face of negative investor sentiment.

I'm still bearish on ( EWC ) but not as aggressively as I was previously. In fact I think a long ( USD ) short ( CAD ) position may be a better, more direct play to capitalize on the current global macro dynamics.