Everyone knows Excel, use Excel and leverage it to manage their portfolio. Excel even has great integration to pull stock prices directly. But as I found it, once you start doing analysis with minute tick data or with thousands of stocks, Excel won’t be able to hold and process that much data.
That’s where MySQL starts to come in. It’s the first piece of the puzzle to start working with and storing large amount of data and process it efficiently.
What is MySQL?
MySQL is a database management system and used on a wide range of purposes including supporting websites, e-commerce and most importantly data warehousing. For those familiar with the Microsoft Suite, MySQL is essentially a free and better version of Microsoft Access.
How I Use It for Stock Analysis?
MySQL is only one piece of the puzzle. There are 3 pieces I use to complete the picture.
MySQL: Database to store all the data I’m pulling to analyze. This includes stock prices, volumes, financial data such as revenue, income, assets, stock news and much more. Any data I would need to analyze stock is all centralized in MySQL.
Python: Programming language that I use to do all the calculations. Think of it like Excel formulas. Python is a series of formulas you can write to manipulate data and is one of the popular programming languages used in machine learning.
Tableau: This is what I use to visualize my results. There are other free alternatives such as using MatPlotLib, but Tableau has been my go to when I need to quickly create reports.
How to Get Started?
Download MySQL here: https://dev.mysql.com/downloads/mysql
Step 1: Installation
Install MySQL Server: This is the database and will be stored on your computerInstall MySQL Workbench: If you’re not comfortable with command line, this is a great way to start working with the server. It’s used to visually connect to the MySQL Server so you can write SQL code to work with data
Install MySQL Workbench: If you’re not comfortable with command line, this is a great way to start working with the server. It’s used to visually connect to the MySQL Server so you can write SQL code to work with data
Install Connectors: Install based on what tools you want to connect to MySQL. There are Excel, Python, Node.js and many more connectors
Optimize MySQL Server: Though the server works out of the box, there are some configurations you want to change to speed up the performance. Here are some resources to start:
- MySQL Performance Tuning Settings
- Increase innodb_buffer_pool_size: This determines how much data can be held in memory. If you’re working with large data sets, increasing this is critical or your queries will fail.How to increase innodb_buffer_pool_size: SET GLOBAL innodb_buffer_pool_size=402653184;
- Calculating InnoDB Buffer Pool Size
Step 2: Learn SQL
This is the language that MySQL uses. Essentially, it’s lines of code to retrieve, manipulate data and other database functions such as creating and deleting tables. Here are some resources to start:
Step 3: Start Practicing
The best way to learn how to code is through practical examples and practice, practice, practice. If you don’t have any idea on where to start, I would recommend going through this sample project to get you started.