All the gaps need to be filled: Marco Simioni

It's an old Wall Street saying--"All the gaps need to be filled"--and is widely known among traders both for its accuracy and its occasional fallacy. It holds true for many market indexes and many stocks. I'm primarily focusing on the S&P 500 etf: SPY. Gaps in SPX are not as clear as in the SPY graph, which is why I will be referring to the SPY.

Consider up gaps to occur when today's opening price is higher than yesterday's high; and, consider down gaps to be those with today's open lower than yesterday's low. It is clear how all down gaps have been closed by the market so far. Most of the gaps take just a few days to be covered, but some take years. Like big holes or magnets they attract prices back to them. Let's talk first about unfilled down gaps.

Unfilled down gaps have shown the most consistent behaviour. All of them have been closed by next markets' bullish moves. A bull market can take years to recover and close a gap, however, all bear markets have resumed to new all time highs. This is mainly due to the general upside bias of the economy.

The main problem is not if a down gap will be closed, but WHEN. Entering big bear markets like 1929, 2001 or 2008 too early results in huge losses because the down gap can take a lot of time, potentially years, to be filled. Past stock market behaviour has shown down gaps filling 100% of times and this must be considered as a strong potential warning with short trades during even the -50% or more bear markets. The down trend will resume to cover all unfilled down gaps. Concerning long side investments, down gap filling acts as an insurance: waiting for the highest down gap to be filled is neither a waste of money nor of time.

Moving on to unfilled up gaps, different results arise from their analysis.

I collected all the unfilled up gaps in the table below going back to SPY inception in 1993. The first unfilled up gap occurred on February the 3rd 1995. And since that time, it is still not covered. We can count 20 other unfilled gaps from then to the present. And we can count about 20 years since the first of them occurred. Filling in the lowest unfilled up gap, as it appears from the backtest below, would mean the SPY would have to lose more than 75% from today's close at 207,5 (time of writing). We could argue that neither 2001 nor 2008 bear markets managed to fill those gaps, and those 20 old gaps won't fill at all. Never.

Since SPY inception 268 up gaps were filled, with only 21 not filled. 92,73% of times the market acted as expected to. Markets act with symmetry, and I believe these gaps to be filled in order to equal 100% accuracy of down gaps; we can't know the time, but they will. In 1995 there were not only 1 but 6 unfilled gaps, and all of them pointing south more than 70%. I think about them as the most dangerous 6 holes in the Wall.

(If you're interested in more articles like this, subscribe to our newsletter! Newsletter subscribers received priority access to this article and will receive unique articles in the future. Thank you!)

The was a guest article written by Marco Simioni of Nightly Patterns. Many thanks to him for his insights on gaps in the SPY.

How to Download Code from Any Public Github Repository

Before we get into any more coding, this post may be of use to some of you. The following guide will show you how to clone, i.e. download, any public repository on github.

If you find it useful, or if you like my other posts, please consider subscribing to Gödel's Market Newsletter. Every sign up is a sign of support!

1. Download and install git. Choose one of the following based on your operating system.

Windows: git-scm.com/downloads 

Mac OSX: git-scm.com/downloads 

Linux (ubuntu): sudo apt-get install git

2. Find a repository you want to download off of github. For instance, the code for our last post is at https://github.com/godelsmarket/stock_database_updater.

3. Open up Git Bash if you're using Windows or Mac OSX. If you're using Linux you can use a standard terminal, because that's basically what Git Bash is.

4. Type the following:

git clone https://github.com/godelsmarket/stock_database_updater.git git_code

You can change "git_code" to whatever name you'd like. It is the directory (and path) that will be holding the code.

5. You're done! You now have all the code in that directory for your use. Try downloading some other repositories.

In the future I'll show you how to create your own repository on Github and how to contribute to public repositories. It's really pretty simple.

If you enjoy these posts, consider subscribing to Gödel's Market Newsletter. It encourages me to write more!

Automated Daily Stock Database Updates Using The R Statistics Project

I received a request from pcavatore several posts ago. pcavatore was interested in "database update automation via R script." He wanted to know "how to run a daily task to update prices in the database."

In this article we'll be using R and the RMySQL package to access and update a MySQL database with stock information from Yahoo! Finance. If you do not have R installed, you can grab a copy at http://www.r-project.org/.

(If you're interested in more articles like this, sign up for the Gödel's Market Newsletter. Each sign up is a sign of support. Topic suggestions are always welcome; and, there are currently two other suggestions in the works).

1. Install RMySQL

In the R console run the following:

install.packages("RMySQL")

 Alternatively, you can go to "Packages" at the top of the R UI. Click "Install packages(s)..." and choose "RMySQL".

2. Test the connection to your MySQL database by running the following. Change the user, password, and database name to match your own.

library("RMySQL")

mydb = dbConnect(MySQL(), user='root', password='password', dbname='stockdb', host='localhost')

Make sure to change the parameters to match your database details. Next run:

dbListTables(mydb)

You should see a print out of the tables in your database.

3. Download data from Yahoo using the following:

stockcsv <- read.csv("http://real-chart.finance.yahoo.com/table.csv?s=SPY&a=05&b=17&c=2015&d=5&e=18&f=2015&g=d&ignore=.csv")

Run the following to see the data:

stockcsv 

4. Insert downloaded data into your database. You'll use the dbGetQuery(mydb, query) function along with a query generated from the stockcsv data.

The following assumes you are storing your data in a table called "stock_data" and that the table has the necessary fields.

query = paste("INSERT INTO stock_data (symbol, date, open, high, low, close, volume, adj_close) VALUES('", stock_symbol, "', '", stockcsv$Date[1], "', ", stockcsv$Open[1], ", ", stockcsv$High[1], ", ", stockcsv$Low[1], ", ", stockcsv$Close[1], ", ", stockcsv$Volume[1], ", ", stockcsv$Adj.Close[1], ")", sep="")

dbGetQuery(mydb, query)

5. Generate url. Since we don't want to have to update the url every day, you can use the following to generate the Yahoo csv url for whatever happens to be the current date:

todaydate <- Sys.Date()

today_day = format(todaydate, "%d")

#yahoo months start with Jan = 0, i.e. typical_month_num - 1

today_month = toString(as.numeric(format(todaydate, "%m")) -1)

today_year = format(todaydate, "%Y")

url_create = paste("http://real-chart.finance.yahoo.com/table.csv?s=", stock_symbol, "&a=", today_month, "&b=", today_day, "&c=", today_year, "&d=", today_month,"&e=", today_day, "&f=", today_year, "&g=d&ignore=.csv", sep="")

6. The Script

Putting it all together we get something like the following script. Can also be seen at its github repo.

library("RMySQL")

mydb = dbConnect(MySQL(), user='root', password='password', dbname='stockdb', host='localhost')

#get today's date
todaydate <- Sys.Date()

today_day = format(todaydate, "%d")
#yahoo months start with Jan = 0, i.e. typical_month_num - 1
today_month = toString(as.numeric(format(todaydate, "%m")) -1)
today_year = format(todaydate, "%Y")

#stock symbol
stock_symbol = "SPY"

#creating the url
url_create = paste("http://real-chart.finance.yahoo.com/table.csv?s=", stock_symbol, "&a=", today_month, "&b=", today_day, "&c=", today_year, "&d=", today_month,"&e=", today_day, "&f=", today_year, "&g=d&ignore=.csv", sep="")

#print out url_create for sanity check
url_create

#download and read in the csv
stockcsv <- read.csv(url_create)

#create INSERT query
query = paste("INSERT INTO stock_data (symbol, date, open, high, low, close, volume, adj_close) VALUES('", stock_symbol, "', '", stockcsv$Date[1], "', ", stockcsv$Open[1], ", ", stockcsv$High[1], ", ", stockcsv$Low[1], ", ", stockcsv$Close[1], ", ", stockcsv$Volume[1], ", ", stockcsv$Adj.Close[1], ")", sep="") 

#run query to insert into database
dbGetQuery(mydb, query)

#disconnect from db
dbDisconnect(mydb)

7. Automation. Run the script from a cron job or Windows task once a day after the market closes and your database will be kept up to date!

If you enjoyed this article and would like to see more like it, consider signing up for the Gödel's Market Newsletter. Each sign up is a sign of support. Topic suggestions are always welcomed and always encouraged.

Calculate Number of Up Days Using MySQL Stock Database

I want to show a simple technique using a MySQL stock database. This could potentially be used to give insight into how a stock is currently trending. We will calculate the percentage of "up" days over a given interval.

1. Choose your favorite symbol.
   
2. If you already have day info in your database, skip this step. If you have minute info, you'll need to extract each day's open and close data. Use something like the following (switch in your favorite symbol for MSFT) :
   
   CREATE TEMPORARY TABLE IF NOT EXISTS temp_open_close AS (
   SELECT DATE(s1.date) as date, s1.open as open, s2.close as close
   FROM stocks.stock_prices_minute s1
   INNER JOIN stocks.stock_prices_minute s2
   ON DATE(s1.date) = DATE(s2.date)
   AND s1.symbol = s2.symbol
   WHERE s1.date LIKE '%6:30:00'
   AND s2.date LIKE '%12:59:00'
   AND s1.symbol = 'MSFT')
   
3. Now that we have daily open and close info, let's calculate the day's up/down value. A "1" will represent an "up" day, and a "0" will represent an "unchanged"/"down" day. Use the following:
   
   CREATE TEMPORARY TABLE IF NOT EXISTS temp_values AS (
   SELECT date, CEIL(ln(close/open)) as upvalue
   FROM temp_open_close)
   
   - CEIL() is a function that rounds a value to the next highest integer. (Note: If you're dealing with stocks that increase >100% you will get more than "1" for those up days).
4. Now we want to find the total up days over a specified period of time.
   
   SELECT COUNT(date), SUM(upvalue)/COUNT(date)
   FROM temp_values
   WHERE date >= SUBDATE('2013-11-11', INTERVAL 40 DAY)
   
   

Conclusion:

This will print out the number of trading days along with the fraction that were up. (This example starts on 2013-11-11, but could be started from any point in history). In this case I get 

28	trading days with a 53.57% of those being "up".

If you're interested in seeing code that will populate a table with rolling values of this data, sign up for the free Gödel's Market Newsletter. I will be sending out code later this week.

Interviewing the Quants: Dan of Theta Trend

Welcome back to Interviewing the Quants. This is the third interview in the series. Many of you might know our guest from his blog on simple objective options trading. It is called Theta Trend. His name is Dan. He's provided some information to give us some insight into who he is, what he does, and how he does it.

(Note: If you enjoy the following, consider signing up for the free Gödel's Market Newsletter. Two more questions and replies from Dan will be released through the newsletter. For this exclusive content, sign up here.)

GM: What originally interested you about finance and the markets?

TT: I don't know that I ever remember not having an interest in financial markets.  When I was really young, I remember my Dad explaining how to read Stock and Bond quotes in the newspaper.  Note that this is well before the internet existed.  I was always more interested in Bonds than Stocks because I liked the idea of receiving interest payments, but I have been interested in markets ever since I was introduced to them.

GM: What caused you to move towards options?

TT: Towards the end of High School, I set up an account to day trade stocks.  It was sometime in the late 90's and I was hoping to skip college and become a professional trader.  At the same time, I started reading some of the McMillan options books and became very interested in options.  I didn't start trading options at that time, but I credit that for some of my background and initial interest.

When I was in Graduate school, I took a series of classes on trading and traded a small account of school funds.  During that time I began trading options because I liked the ability to construct position that needed to be less directionally correct.  I felt that options gave me a better a chance to win and I started combining options with other (mainly trend following) strategies.

GM: What type of hobbies, interests, and education background do you have?

TT: I have something of a one interest, compulsive personality and that's reflected in my small list of other interests.  Outside of trading, I love to cycle, run, and be active outside.  However, I have essentially no interest in any sport sport that involves a ball.  Do cats constitute a hobby?  I'm not sure, but I have two cats.  They seem to like me most of the time, which I view as a compliment.

My educational background is a little mixed.  I have an Undergraduate degree in Economics and a Master's degree in Accounting.  I spent the majority of my time in Grad school watching the markets during class and pretending Accounting didn't exist.  Strangely, after leaving Graduate school I became a CPA and worked as a Tax Accountant for 5 years.  That experience helped me realize that I genuinely dislike working as an Accountant and I recently left my job to move and also pursue something more market related.  There's a recent post on my site called, "What I've Learned From Living Wrong," that talks a little bit about my background and some of the changes I'm making.

GM: How long have you been trading?

TT: I have been trading on and off since the late 1990's and much more seriously since late 2007.  Even when I wasn't trading I was watching markets and reading financial/economic news.

GM: What are some fundamental ideas or principles that arise in your work?

TT: I believe that markets trend and that it's essential to validate trading ideas.  Additionally, it's incredibly important to always be asking questions and trying to learn.  It's equally important to ask why something is working and why something is not working in the markets.

GM: What lead you to start Theta Trend? Do you find benefit from discussing your ideas in a relatively open and public setting?

TT: On a practical level, I started the blog primarily because I wanted to have a discussion about combining Trend Following with Options and it didn't seem like that discussion was taking place.

I have benefited from the blog in more ways than I ever imagined possible.  The site has introduced me to numerous people and other writers who have an interest in markets.  Those relationships combined with writing and reflecting continue to make me a better trader.  I can't say enough good things about writing the blog; the experience continues to surprise me in positive ways.

--

To receive two more questions and answers from Dan at Theta Trends, sign up for the free Gödel's Market Newsletter. I will be sending them within the next few days.

Please thank Dan for the interview. For more information, insight, and knowledge, visit his site Theta Trend and check out his free guide to becoming a better trader.

Setup Your First MySQL Stock Database

Today we’re going to setup a MySQL database, populate it with some data, and run a simple query. More complicated stuff to come, but this is a great start for those who have never used a relationship database management system (RDBMS) before.

(Note: Sign up for the new Gödel’s Market Newsletter if you find the following useful and want more articles on useful information, including interviews and research.)

Some may say that learning to use an RDBMS takes too much effort. But, depending on how you’re doing your data storage right now, an RDBMS could easily help you organize better (aren’t those CSV/TXT/Excel files piling up?), give you results faster and even give you greater insight into your data.

For this article we’re going to use MySQL. It is among the most popular RDBMS’ out there, and it is open source and free. Many sites use it, including Facebook, which serves over 1 billion people tons and tons of data.

Steps:

1. You’ll want to download a copy at the following link:
   http://dev.mysql.com/downloads/mysql/ (make sure to select the download appropriate for the operating system you are using).
    
2. Installation should be fairly automated. Just click next and make sure you don’t install any “Ask Jeeves” adware…Make sure you do a full install which should include the MySQL Workbench. This will be our method for working with the MySQL Server.
    
3. Next, grab some sample data from Yahoo. Go to your favorite stock ticker, and select “Historical Data” on the left hand side. Then go down to the bottom of the page and click on “Download to Spreadsheet”. Here’s a link for MSFT: http://finance.yahoo.com/q/hp?s=MSFT+Historical+Prices This will download a file called “table.csv” which is exactly what you want. If you open it up you’ll see your stock’s data (date, open, high, low, etc).
    
4. Now open up MySQL Workbench CE 6.* (I’m using 6.2, but they should all be very similar).  You should have a database called “local”. Double click on that. If you need to setup a database you’ll want to create one locally. There will be a “root” user which should be fine for our purposes at the moment. Assign a password and connect.
    
5. Now that you’re connected to the database, take a look at the left side. You’ll see something called “Schemas”. We need to create a new one for stocks. You can either right click below the existing Schemas and select “Create Schema” or you can go up to the top bar and click the icon “Create a new schema in the connected server.”
    
6. For your new schema, name it something like “stocks”. Use the default “Collation” and click “Apply”. Click “Apply” again on the creation script and again if needed. Then click “Finish” and you should have a new schema to the left.
    
7. Click on the drop down arrow to the left of your new schema. Right click on “Tables” and choose “Create Table…” For table name choose something like “historicaldata”. Go down to Column Name and double click below it to add a new column. You’ll want a column for every row in your “table.csv” file. So one column for “Date”, “Open”, “High”, “Low”, “Close”, “Volume”, “Adj Close”.
   
   For Datatypes, you’ll want “DATETIME” for “Date”. For “Volume” you’ll want to use “INT”. For the others you’ll want to use “DECIMAL(10,3)”. You can use other data types, but these work well enough for what we’re doing.
    
8. Now right click on your new table “historicaldata” under “Tables” within your newly created schema on the left hand side. You’ll want to choose “Select Rows”. This will bring up all the data you currently have in the table, which should be none. Go to the middle portion where you’ll see some icons following “Result Grid”. Go to the “Export/Import” tag, and click on the “Import records from an external file” icon.
   
   This will let you choose a “CSV” file for import. Before you choose “table.csv” open it up and remove the first line with the headers (i.e. Date, Open, High, etc etc). If you don’t, this will also get imported (there are ways around this, but I’m keeping this intro simple). Now you can choose  your “table.csv” file for import. It’ll take a little bit to import and then should load up the data on screen.
    
9. Now you can run your first query. Go up to the icon with “SQL” and a little plus on it. It should be right below “File” at the top. This should open a new tab for SQL queries. Type the following and press “CTRL” + “ENTER” or click the “Execute” icon (a little lightning bolt).
   
   SELECT * FROM stocks.historicaldata
   
   This should return everything from your table. Not very useful, but it’s a start. Next try something like:
   
   SELECT * FROM stocks.historicaldata WHERE date > ‘2015-03-01’
   
   Try:
   
   SELECT * FROM stocks.historicaldata WHERE open > close
   
   I’m sure you can come up with some more on your own! There will definitely be more on this in the future.
    

These steps are only the very beginning. You can load multitudes of stock data into the table you’ve created (you may want to add a “Symbol” field to differentiate new stock symbols you add). There are many ways to analyze the data, and if you want you can even analyze it externally (say, in Python, C#, Java or even Excel).

I’ll be posting more on how to continue on from here. In the meantime, sign up for the Gödel’s Market Newsletter. Information not available on the blog is sent out through the newsletter. Click here to sign up!

Best of luck.

Interviewing the Quants: Marco Simioni and Nightly Patterns

Welcome back to Interviewing the Quants. This will be the second interview in the series. Many of you might know our guest from his trading at Nightly Patterns (http://nightlypatterns.wordpress.com). His name is Marco Simioni and he’s agreed to give us greater insight into what he does and who he is.

(Note: This interview took place via email and has been formatted as an article.)

GM: What lead you to quantitative trading?

MS: Well, my first approach to quantitative trading was while still at university during my first Statistics course. I was used to math, but probabilistic theory completely changed my view on trading. I remember before that course how focused I was on Elliot and Gann theories. Both work, but what I was lacking was an objective approach to trading. I switched from one oscillator to another, indicators and classical technical analysis patterns. But I was still looking for a more systematic way to trade. Finally I came across Michael Harris and Larry Connors' research.

GM: What's your background academically?

MS: I studied Mechanics at high school. Instead of going on with engineering at university I changed my mind and started Economics. I got a Bachelor Degree in Marketing and Management at Cà Foscari University with a thesis in Statistics focusing on Auto Regressive Integrated Moving Average processes. I then specialized with a Master Degree in Economics and Finance with quantitative and risk management oriented studies in the same University with a thesis on a historical analysis of first days market behavior of tech IPO's. All of this apart from my more than ten years of trading studies...

GM: How long have you been trading? What caused you to choose overnight trading?

MS: I've been trading since 2006. I am 28 years old. Despite my quantitative overnight trading I do use many other strategies too. I'm a fan of ETF rotational strategies, VIX ETP's quantitative systems, and mean reverting stock swing trading. My academic background led me through Graham fundamental studies, Greenblatt's formula, and O'shaughnessy's portfolios. I became an overnight trading researcher mainly because it’s uncorrelated to other strategies I use, which results in greater portfolio diversification. And it seemed nearly nobody cared about its profitability at all.

GM: Which principles do you find most useful?

MS: I believe there are two core market principles: a mean reverting regime and a trend following regime. Here sometimes financial academics studies are preceded by traders' experiences. The first more intuitive effect deals with overbought and oversold market conditions. Nearly 70% of all my patterns are mean reverting. I'm constantly researching momentum patterns too, but they are much more difficult to discover. I believe momentum market studies make the difference, because they can prevent false or losing mean reverting trades. So many systems do not work properly mainly because they are not able to detect the differences between these two key market principles. 

GM: Are there any ideas that you find yourself going back to again and again? If so, what are they?

MS: Money management strategies. I'm referring to Kelley's formula, Vince works, Thorpe's studies, Larry Williams's or again Turtles' approaches. I could add many other equally brilliant researchers like Ziemba, Lundstrom and many others and still I would miss someone. I believe whatever system or strategy you trade, money management is vital to survive the markets and deal with high leverage. I'm constantly looking for a better position sizing technique, as I believe something more is still out there to be found.

GM: Why did you decide to create the NightlyPatterns blog?

MS: Trading successfully is difficult. I remember a study from Taiwan stock exchange that highlight the % of successful traders from their account’s net results. It seems that 95% of futures traders after 6 months end burning up all their starting equity. It takes years of researching and testing to build a consistent and successful trading strategy. And when a trader succeeds with it, he keeps it a secret. I started Nightly Patterns blog as a free trading diary, and then I decided to move it to a more professional service. There are so few really successful futures trading rooms that I decided to enter the business. 

GM: What is something most people might find surprising about you or your trading style?

MS: Many traders and bloggers are surprised by my young age. Volatility has kept increasing during the last ten years. Huge algo-trading with “robot” volumes, high tech 2000 end bubble, subprime crisis, commodities crashes, sovereign debt crisis, zero interest behavior environment and currency wars completely changed many old market dynamics. Despite my ten year experience, I believe more in quantitative back testing than a thirty or forty year market experience. This is my "quant" view.

GM: Are there any suggestions you would give to an aspiring trader?

MS: Just read whatever you can about trading before trading. Do not trade first. Just read. Read a lot. Don't get lost in the academic finance research, but don't get lost in all those unhelpful trading books out there either. Read one academic paper from a university teacher on markets and investments and then one trading book from a trader. Do this again and again. Look for your own trading style. Take something from all the books and papers you read and collect everything. Then read blogs and all the website can teach you something. Your trading success will come to you before trading. And of course try to follow some well established traders and mirror them, you will survive the markets.

GM: Thank you for sharing with us today. Do you have any last thoughts?

MS: My last thoughts go to consistency and systems diversification. If you have a system, follow it. This is vital. Follow your system, not your gut. Do not cherry pick your strategies; this will lead you to bankruptcy. You will find yourself taking the worst trades and missing the best ones. If you have a winning system just trade it but always look for another one to add to your trading arsenal. This will lead your results to approach Shannons' Demon Effect. I wish you all a good trading. Thank you very much for your attention.

GM: Thank you again Marco. Thank you everyone else for reading, and please visit him at Nightly Patterns.