Creating Options Database

Question

I am trying to create a database which will hold information for various stock options and will need to be updated daily. The idea is to use this database to keep track of changes in the open interest and use that for trading and research.

I am using powershell to automatically download the data every morning from the following website: http://www.theocc.com/webapps/series-search?symbolType=U&symbol=IBM

The downloaded file contains the ticker symbol, strike price, expiration date, call OI and put OI and some other items that I don't care about.

The purpose of this database is to track the OI changes on a daily basis and create reports.

I don't have much experience with database designing so I am not sure how to proceed. Which database will be best to use? MSSQL, MySQL, NoSQL, etc.? I think I will need the above mentioned columns and a column for the datetime stamp of the record. Am I on the right track?

I would like to automate this as much as possible. If there is something out there that is already able to do this I might use that as well rather than recreating the wheel. I am open to suggestions on how to get the best results out of this.

Thanks

Answer 1

I don't have much experience with database designing so I am not sure how to proceed.

Read an introductory database book!

Which database will be best to use? MSSQL, MySQL, NoSQL, etc.?

MySQL and Microsoft SQL Server are database management systems that follow the relational database paradigm. NoSQL is not a database management system, it is a paradigm that is contrasted with relational databases. Examples of NoSQL DBMSes include MongoDB, Redis etc.

Unless you know a handful about database optimizations, there is no strong reason to prefer MySQL, MSFT SQL Server, SQLite, kdb, Redis or any DBMS. You should use what you are comfortable with learning, have reasonable community support and documentation for, and meets your budget and operational requirements:

• Is it open source?
• Is it more suitable for a UNIX or Windows environment?

You should only start worrying about what DBMS you're using if you are making $10^3+ cost trade-off decisions, thinking about servicing >30 (exact number varies depending on your hardware) user requests, or are storing more data than would fit on desktop computer that is heavily spec'ed out, or are hitting the bandwidth limit either at the network, disk or memory layer, because there's very little difference between DBMSes until you start attacking these types of issues.

If you would like a specific answer, I would actually recommend something like MySQL. It's commonly used, there's a lot of reading materials available for free on how to use it, and it conforms to the SQL standard so you can extend your knowledge to other DBMSes once you've familiarized yourself with it. It's a bad idea to use a NoSQL DBMS with absolutely no knowledge of the relational paradigm, because NoSQL DBMSes trade-off certain functionality to meet specific needs that are difficult to service with a relational DBMS. However, if you don't know what a relational DBMS is, it's unlikely that you'll understand this trade-off.

People who first learn to use a DBMS often fall into the trap of thinking that the newest DBMS is the most modern and most powerful all-around - this is completely incorrect. Research into DBMSes is very mature, and even 20-30 year old DBMSes are heavily optimized and suitable for modern use cases. Many elements of the architecture of classical relational, row-oriented DBMSes are still reused in elements of NoSQL/column-oriented DBMS design. The modern DBMSes don't evolve out of a need to outperform older DBMSes, rather, they evolve out of a need to trade-off certain functionality to meet more domain-specific needs (e.g. Hadoop workload, data model flexibility, multi-data center resiliency). Then you also have a plethora of new DBMSes that don't do anything new but are really just created to help their founders pocket venture capital - again, you won't really know this until you are familiar with the basics of database management.

I think I will need the above mentioned columns and a column for the datetime stamp of the record. Am I on the right track?

No, you will need more than that. You should have constraints to ensure integrity of your data; indexes to speed up your queries; normalization to minimize the redundancy of your data, and so on. You have just identified columns that you will need in 1 table in your database.

Answer 2

You are in the right track. I've been in the same situation, you won't find anything out there, so I end up to create my own database to track daily stock options.

In my case I use ms sql server, but you can use the others you named as well.

Also, keep in mind that the daily data is huge, and your database will grow considerably over time.

I've been collecting daily option for All optionable stocks and the size of the database is more than 6gb (I also save all the Greeks and different IV values)

Good luck.

Answer 3

Don't use a nosql DB like Mongo. Use an SQL like MySql or MSSQL.

MSSQL: https://www.microsoft.com/en-us/download/details.aspx?id=42299 MYSql: http://dev.mysql.com/downloads/mysql/

Sounds like you have structured columns like a spreadsheet. If you are a fiance guy you, intuitively you see data the way it is stored in SQL. Tables and columns.

If your tech stack uses lots of Microsoft products like VBA, Visual Studio, and Excel, go ahead and use MSSQL. But double check if doing trading violates any commercial licensing type of stuff.

If there are licencing problems or you don't want the Microsoft ecosystem you can use MySql.