New Forum

Visit the new forum at

Sunday, August 30, 2015

Large Down Mornings in SPY

What has happened in the past when the SPY opens 3.5% or more down?

I ran the numbers and have some code for doing it yourself. I found that there's typically a profitable day ahead, and even if not profitable, the day tends to have enough upside (Open to High) to be of great interest.


Here is a link to the code I used to calculate these results. You will want to grab the DataManager from Github to easily populate data for the study.

Check out the topic on the new forum if you have any suggestions or want to discuss the topic in more detail. Or if you just want to talk about your favorite beer :)

As always, consider signing up for the newsletter. It encourages me to produce more code and analysis, and will provide content not found on the site.

Sunday, August 16, 2015

Downloading Stock Market News for Specific Symbols

Grabbing the data.

How do you grab the latest news on your favorite ticker symbol?

It all starts with the following URL.
You'll want to change "q=SPY" to whatever symbol you're interested in.

You can add something like the following to the end if you'd like more articles returned. There is, however, a limit on the number returned.
What the returned data means.

When you download the above link, you're given an rss feed, which is in XML. If you don't know XML, it's basically a text document with various fields sorted by tags that look like "<tag>info</tag>" (e.g. <title>SPY Stock News</title>, <link></link>, etc).

The tags you'll be interested in start with <item>. Each <item> contains info on a single news article. Within the <item></item> tags, you'll find four tags that are probably of interest to you. They are:
<title> This gives you the title of the article. (e.g. <title>S&amp;P 500: Should You Buy the SPY ETF Now?</title>
<link> This gives you the link to the article. (e.g. <link></link>
<pubDate> This is the date the article was published online. (e.g. <pubDate>Tue, 30 Jun 2015 12:00:10 GMT</pubDate>) 
<description> This is the description Google gives for the article. It is short but provides an idea without requiring you to download the entire article. (e.g. SPDR S&amp;P 500 ETF Trust Sees Large Drop in Short Interest (SPY))
What you'll likely want to do is download this data and extract it somewhere you can store and analyze it.

Storing the data.

I'd store it in a database. You can also store it in flat files (.txt or .xml) if you prefer.

Create a table in your MySQL database (make sure you have MySQL installed! On Ubuntu Linux: sudo apt-get install msyql-server). This can be modified for other databases fairly easy.
CREATE TABLE stocknews (symbol VARCHAR(5), pubDate DATETIME, title TEXT, link TEXT, description TEXT);
The Code.

Next we want to extract and insert. We'll use Python.

First, if you don't have the "pymysql" module installed you'll need to install it by typing: pip install pymysql. I also really like "timestring" for date/time parsing (run: pip install datestring).

Now for the lovely code :) This can be more easily consumed via the "godelsmarket" Github Repo.

import urllib2
from lxml import etree
import pymysql
import timestring

#connect to the database
connection = pymysql.connect(host='localhost',

#stock symbol you want to download news for
symbol = "SPY"

#this is the url where we grab the data
url_stub = ""

#use urllib2 to download the data
response = urllib2.urlopen(url_stub + symbol)
xml =

#turn into an xml doc
doc = etree.fromstring(xml)
#we're only interested in tags under <item>
item_tags = doc.xpath('//channel/item')
for item in item_tags:
#split up by the four tags
  date_tag = item.xpath('pubDate')
title_tag = item.xpath('title')
link_tag = item.xpath('link')
description_tag = item.xpath('description')

date_text = date_tag[0].text
title_text = title_tag[0].text
link_text = link_tag[0].text
description_text = description_tag[0].text

print 'date:' + date_text
print 'title:' + title_text
print 'link:' + link_text
print 'description:' + description_text

#insert into the database
with connection.cursor() as cursor:
sql = "INSERT INTO `stocknews` (`symbol`, `pubDate`, `title`, `link`, `description`) VALUES (%s, %s, %s, %s, %s)"
cursor.execute(sql, (symbol, str(timestring.Date(date_text)), title_text, link_text, description_text))

As always, if you have any questions feel free to comment! Hope you enjoyed!

If you enjoyed this article, consider signing up for the Gödel's Market Newsletter. I don't spam, and every signup is seen as a sign of support! Thank you!

Saturday, July 25, 2015

Finding Stock Splits and Ex-Dividends Using R

The following post shows you how to check for any stock splits and ex-dividends happening. It'll spit out a list of symbols. These can be used as alerts or piped into another script for updating your database as needed.

I want to thank pcavatore for suggesting this topic! Comments and suggestions help drive the content of this site. Now, onto the explanation. If you're just interested in the code, skip to the bottom or check out the github repo.

1. Requirements. We'll be using the "XML" library. In R, it makes it easy to extract tabular data from webpages. Running the following code will install "XML" if it isn't already installed. Then it will load the library for use.
if (!require(XML)) install.packages('XML')

2. URL selection. We'll need some online location from which to pull the data. Yahoo! has easy to grab stock split info; has easy to grab dividend info. So we'll use those.
#for stock split
url <- "" 
#or, for dividend information
url <- ""

3. Download the data. We use the readHTMLTable function in order to grab the data.
tables <- readHTMLTable(url)

4. Extract desired data. The previous command will return more tables than you're interested in. Within those tables is also more data than we're interested in. So, we'll print out just the specific table and data column we'd like, now that the tables are stored in memory.
#for stock split data you only want the 6th table and the 3rd data column
#for the ex-dividend data you only want the "Table1" table and its 1st data column

5. The complete code. It can also be found on github, here.

if (!require(XML)) install.packages('XML')
# Check for stock splits
url <- ""
tables <- readHTMLTable(url)
# Check for ex-dividend
url <- ""
tables <- readHTMLTable(url)

6.  Conclusion. You should now be able to get a printout of any stock splits or ex-dividend dates occurring. This can be used to help update your database or keep you alerted to potential trading opportunities.

7. Further. The above outline can be used to download other tabular data. The basic outline is: find a site with tabular date, download that data, and then extract the tables and columns you need.

(If you've enjoyed the above article, consider signing up for the Gödel's Market Newsletter. Every signup encourages me to write more. Thank you!)

Friday, July 17, 2015

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.

Sunday, July 5, 2015

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.
Mac OSX: 
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

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 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!

Saturday, June 20, 2015

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

(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:
 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.
mydb = dbConnect(MySQL(), user='root', password='password', dbname='stockdb', host='localhost')
Make sure to change the parameters to match your database details. Next run:
You should see a print out of the tables in your database.

3. Download data from Yahoo using the following:
stockcsv <- read.csv("")
Run the following to see the data:
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("", 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.
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("", 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
#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
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.

Tuesday, May 5, 2015

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) :

    SELECT DATE( as date, as open, s2.close as close
    FROM stocks.stock_prices_minute s1
    INNER JOIN stocks.stock_prices_minute s2
    ON DATE( = DATE(
    AND s1.symbol = s2.symbol
    WHERE LIKE '%6:30:00'
    AND 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:

    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)


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 
28trading 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.