Thursday, March 26, 2015

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.


  1. You’ll want to download a copy at the following link: (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: 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’


    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.

Monday, February 16, 2015

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 ( 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.

Sunday, January 11, 2015

Interviewing the Quants: An Inside Look With “Sanz Prophet”

Our first guest for the “Interviewing the Quants” series is Vangelis Maderakis. Many of you probably know him as Sanz Prophet through the eponymous blog ( Recently, he became a partner at Logical Invest, a successful business dedicated to logical, rules based investing.

I’ve asked him if he would answer some questions, hopefully giving readers in the quantosphere greater insight into the mind of an online quant. So, even before we begin, please thank Vangelis Maderakis for joining us today!

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

GM: What experience did you have with finance before you started?

VM: Although I did start as an engineer and hold a B.A. in Economics I ended up following my passion at the time, filmmaking. I have trained as a director/producer and have directed commercials for a living. I started trading and managing money during the 2007 crisis and discovered I like the quant side of it. I have been developing systems and trading ever since.

GM: How do you develop most of your ideas?

VM: Reading books, research papers and blogs. I like to backtest all kinds of ideas. From obscure fx strategies to option strategies that sound ‘logical’ but do not stand up to scrutiny. At one point you realize the market is just a sum of all participants and will adapt onto itself. Then you start asking simple questions: How will the rise of Wealthfront and other similar services affect the market? Or as a private investor what are my advantages vis-à-vis the larger players or even ETF issuers?

I believe there are good ideas to be found in the public sphere. Like in many areas of life the difficulty is not in finding the idea. It is in actually deploying it into the real world. Many people will stop at the first stage.

GM: What inspired you to write a blog about finance?

VM: The quant blogging community. People like Michael Stokes of MarketSci who was not just enlightening but also entertaining. I also felt the need to reach out to other independent minded investors and bloggers and exchange ideas.

GM: What is the most essential or fundamental lesson you've learned from the markets?
VM: To have a plan or strategy. To simplify. To be patient. To think outside the box. To try not to lose rather than trying to win. Also I have learned that markets are counter-intuitive and extremely unjust. It’s like flirting. Trying too much does not guarantee success. You need to be open minded and flexible. You need to care. But you cannot be desperate.

GM: What is your favorite type of market and why?

VM: A sideways moving market. From a risk management perspective, a bull market is as challenging as a bear market. A system developer who is conservative will likely underperform a straight bull run because they need to protect the account from a possible unexpected event.
In a sideways market one can trade with less risk since the average participation time is usually low. So from a risk management point of view, a side market can be desirable given one can find the system to exploit it.

GM: What are ideas you find yourself returning to again and again?

VM: Under-deployment of funds and mismanagement of the ‘big-picture’. That is my most common concern.

A private investor will likely split up their available capital and buy different kinds of assets, strategies, individual stocks, etc. It’s a natural instinct to diversify and not put all eggs in one basket. The problem is that there is no easy way to tell how her whole portfolio behaves. Is it truly diversified? Is she deploying all available capital or risking too much in small risky instruments while the bulk is sitting idle in cash?

So what I find myself looking for is a ‘total’ strategy that supersedes sub-strategies and can track and adaptively allocate to them. A meta-strategy of strategies. That is one of our goals at Logical Invest.

The other idea is more philosophical. I find myself thinking that in trading as in life, the main truly scarce and non-tradable asset is time. Since investment is really a compounding game, any mediocre young investor will most likely outperform by a large margin anyone who is double his age. Of course the young investor doesn’t know this. So the older person needs to guide him.

GM: So that the younger person is able to utilize those extra years wisely?

VM: Yes. It would be smart to invest early on but one doesn’t even need to be that smart an investor. I would argue that on a 30+ year timeline being mediocre is sufficient. The assumption, of course, is that the money survives the course of unpredictable foolish human passions, socio-political changes and all the things that hopefully our kids will get to experience that are more interesting than money
GM: How did you come to join Logical Invest?

VM: As a quant blogger, I was exchanging ideas with Alex and Scott. Part of those ideas were testing Frank Grossmann’s strategies. Eventually we met Frank and he invited us to join Logical. So over at Logical, we still develop strategies but now we can be challenged and disciplined into a steady flow of production. It’s quite exciting. We feel we are operating in the fringes of the financial world offering institutional+ level strategies for $30. It’s quite outrageous, really. Some of our subscribers are wealth managers. Recently a lot of physicians have joined. So you can say that at this point we cater to self-minded, educated individuals.

GM: Do you have any final thoughts for readers?

VM: A quote by Dennis Richards:
“I don’t think trading strategies are as vulnerable to not working if people know about them, as most traders believe. If what you are doing is right, it will work even if people have a general idea about it. I always say you could publish rules in a newspaper and no one would follow them. The key is consistency and discipline.”

GM: Thank you very much for sharing with us Vangelis Maderakis. Please visit him at Logical Invest and his blog Sanz Prophet. 

Tuesday, December 30, 2014

Free Full Version of Microsoft Visual Studio 2013

It’s been a couple months since Microsoft released the professional version of Visual Studio 2013 for free under the title Community Edition. Meant for non-enterprise environments, it still has all the features of the professional version and is just as powerful.

With QuantConnect just about to open sourcing their Lean engine (sign up for a hobbyist account to support this!), it’s the perfect time to update and prepare your development environment.

VS 2013 CE is somewhat hidden away on Microsoft's site. In order to download a copy go to and scroll down to “Community 2013 with Update 4”. Click on the title of the purple bar and go to “Install Now” ( Download and install, and you’re on your way to a professional dev environment!

Tuesday, December 9, 2014

Barbell Strategy Backtest

QuantConnect is working towards the support they need to open source their code. To promote their open source campaign, I’ll be converting a simple version of the barbell strategy into a QuantConnect backtest. If you want to run the code without reading anymore, feel free to sign up at QuantConnect and clone my code! And if you enjoy QC, consider $10/mo a bargain for contributing to the next generation of backtesting. I personally can't wait to start looking at their code :)

Onto the article!

The following is a simple take on the idea presented over at “Don’t Fear the Bear” (DFB from now on). Basically, we’re going to place approximately 20% of our portfolio in a high risk instrument and place the other 80% into a low risk instrument. DFB suggested XIV as the high risk instrument and cash as the low risk instrument. We’ll be doing the same.

I suggest you clone my backtest on QC and play around with the dates, symbols and various other values to get an idea of how the code works. If you want to generate the backtest code from scratch (i.e. not clone mine), you can clone QC’s “Basic Template.” All of the barbell strategy code was derived from a “Basic Template” clone.

We’ll be using the following line to tell QC which symbol we want to trade:

public string symbol = "XIV";

Now, wherever you see “MSFT” replace it with the word symbol. Do not use quotation marks. For example:
AddSecurity(SecurityType.Equity, "MSFT", Resolution.Minute);
would become:
AddSecurity(SecurityType.Equity, symbol, Resolution.Minute);

What this replacement does is inserts whatever value we have symbol set to in each location. This will make it easier to change the symbol value if you ever wanted to. Instead of updating each of the locations, you can just update the line public string symbol = “XIV”; to public string symbol = “SPY”; and the backtester will know you want to use “SPY” instead of “XIV”.

Changing the amount within the setCash(); method call determines how much money you want the strategy to be tested with. I set mine to 100000. This doesn’t have to be changed, but it gives you an idea of how to change this value. Try starting with other values, if you’d like.

Next is the code that does contains all of the logic for our system. Two slashes tell the backtester to ignore those lines. They are called comments, and I’ve left you with several to give you an idea of what is going on.

Continue on after the code for more commentary.

======= Portion of Main.cs ===========

//only get daily close
           if (data[symbol].Time.ToString().Contains("3:59:00")) {
//prints out to console window
               decimal currentPercentage = (Portfolio.TotalHoldingsValue / Portfolio.TotalPortfolioValue);
               Debug("CurrentPercentage: " + currentPercentage);
               //reconfigure for 20/80 holding; but give a little room (15%)
               if (currentPercentage < (decimal).15 ){
                   decimal amtToBuy = ((((decimal).2 - (currentPercentage)) * Portfolio.TotalPortfolioValue)/data[symbol].Close);
                   amtToBuy = Math.Round(amtToBuy);
                   Order(symbol, amtToBuy);
                   Debug("Puchased " + amtToBuy + "of " + symbol);
               //reconfigure for 20/80; but give a little room (25%)
               if (currentPercentage > (decimal).25) {
                   decimal amtToSell = (((currentPercentage - (decimal).2) * Portfolio.TotalPortfolioValue)/data[symbol].Close);
                   amtToSell = Math.Round(amtToSell);
                   Debug("Sold " + amtToSell + "of " + symbol);
                   Order(symbol, -1* amtToSell);
           //we don't care if it's not end of day, so return;
           } else {


The first thing you’ll notice is that we’re only looking at minute bars that come from the end of day. That’s what the “3:59:00” check is for. This can be achieved several ways, but this way is fairly easy to understand. If it’s 3:59 PM we can go ahead and continue with our logic.

Anywhere you see “Debug();” is an easy method to print to QC’s console. Very helpful when you’re trying to figure out what is going on with your code!

Next we’ll check the percentage of our holdings that our in XIV. We want this to be 20% at the start. And, we want to rebalance this using some sort of logic. I have chosen to rebalance when XIV gets to 15 or 25% of holdings. You can play with this number. If you’re feeling adventurous, try to rebalance only at the beginning of the month. :)

You’ll see amtToBuy and amtToSell, which are both variables holding the value created by the statements that follow. All I’m asking is for the backtesting program to determine how many shares (rounded to an integer) I need to buy or sell to bring myself back inline with our 20% goal. Nothing too complicated, although I do have to use the (decimal) cast in order to force .2 from float into decimal. Decimal is what’s used typically in the backtesting program (it’s good for money related activities like stocks).

After we find out how much we want to buy or sell, we can put in an order. This is as easy as using the Order(); method; which, is really, really easy. Just make sure your sell amount is negative. That is how QC knows the order is a sell.

I hope the backtest code is otherwise fairly clear: data[symbol].Close gives the price of the symbol you are following. And, Portfolio.TotalHoldingsValue gives the value in dollars of your the symbol you own. Portfolio.TotalPortfolioValue lets you know how much your entire portfolio (cash included) is worth.

The outcome of the backtest isn't amazing, but it is a good start to your backtesting portfolio. Feel free to post questions and other backtests in the comments!

Also, please consider supporting QuantConnect’s drive to open source its code. Becoming a QC member is a small price for access to an amazing product! Just remember, the more people who have access and can work on it, the better the code will become!

Saturday, November 22, 2014

Unlimited Free Backtests with Large Tick Data Source

There are other services out there that allow free backtests. However, QuantConnect is the only service I know of that allows unlimited free backtests with tick data. Let me say that again: they have tick (i.e. time and sales) data going back to 1998--something around 4TB of data. And, if you prefer, or want to utilize it along side the tick data, they also have minute and second data.

QuantConnect even allows you to pull in the backtest data using their easy to use Rest API, or utilizing their (currently in beta) Visual Studio plugin. If you decide to utilize the backtesting portion of the Rest API more than 5 times a day, it’s a relatively cheap upgrade to unlimited api backtests: 19.95. You can always switch back to their Web IDE for continued unlimited backtesting.

Plus, even better, Visual Studio 2013 Community Edition has just been released. It has all the features of the Professional Edition for use with small teams and for personal use. As mentioned above, QuantConnect has a plugin (currently in beta, and open sourced on github) that allows you to easily develop strategies directly from Visual Studio. Given Visual Studio is easily one of the best IDEs on Windows, this is beyond a blessing; and, definitely worth going along for the “beta” plugin ride. This is not available through other services that typically require API access through the browser. Although, I won't forget to mention that QC also has a nice browser-based IDE.

At a bare minimum, I would recommend signing up for free and trying out their web IDE. They use C# which is considerably more powerful than some of the languages used on other backtesting sites. This power comes in handy when you’re dealing with many, many data points and complex algos. It’ll keep things snappy while data is massaged across a number of cloud servers, allowing you to have your data points crunched in no time. And, if you’re new to C#, QC also has good documentation to help you along.

There are also many innate features (e.g. ability to have multiple files in a project) that you will come to appreciate as you delve into their system. They are adding things and upgrading frequently. I continue to expect more in the coming weeks and months. They have good documentation and are easy (and free) to sign up with. Here's another link to their site:

Please, feel free to post any backtests you run in the comments; I’d be interested to see!

Thursday, November 20, 2014

Gödel's Market on Github

There are some snippets of useful code on this blog that could transform into even more useful code if placed in a more conducive environment. I've created a Github account for blog related code here:

Right now I've only had the time to put up a repository for the IB Historical Data Downloader. I had to clean up the code formatting a little bit and transfer over the article's content to a readme to make things a bit clearer.

I'd really like to get all the code from the site transferred over. Then I would like to make it more user-friendly so that individuals who do not have as much experience with programming can get their feet wet. That's the goal :)

If you have any questions on how to make a piece of code do something more interesting than it does, please ask. These are basic starting points. I love to help, but I'd rather not just give all the answers right up front. For one, it's no fun that way (check out Project Euler and tell me, with a straight face, that it would be fun to have the answers given to you...). Second, you'll learn far more trying to develop your own program, which will give you more confidence to create more complicated things. Third, if you want it all done for you, I'm available for hire. :)

Now, back to the data.