Create a Self-updating Stock Price Database
How many times have you downloaded the same time series data to backtest a trading strategy? If you’re like silly old me – too many times…or at least enough times to write about it!
You might say that most APIs like yfinance are fast and easy to use so it’s no biggie. Granted, that is mostly the case. It does escalate from a minor inconvenience to a serious pain in the proverbial though if you want to do a deep dive into minute intervals or any scenario that requires you to throttle your API requests to respect limits.
If I still haven’t convinced you, downloading the same time series repeatedly is, at the very least, a gross inefficiency and as programmers we hate inefficiencies, so let’s squash it!
Alright, that’s enough of the BS. Let’s dive straight into the code.
From line 50 onwards I’ve set out the basic config, which includes the columns required (in this case just the close prices), the tables with respective tickers and a dictionary to store the updated prices. There are 2 tickers within each of the 3 financial markets to give you an idea of how you might include multiple tickers within multiple tables. Crypto, currencies and stonks…ahem…stocks.
Line 58 creates our all important database connection.
Line 60 iterates over the database tables and from there a ticker string is created to be passed into the yfinance API.
Lastly, line 62 calls the update_database function, passing in the respective table, tickers, database connection and columns required.
The update_database function firstly utilises a Try block to check if there are any existing prices for each table in the database.
If no data exists, an exception will be raised, triggering the Except block. In this instance we know we’ll need to set our start date to the inception of the time series.
If prices are returned from the database in the form of the db_response dataframe, the code progresses to the Else block and simply looks to find the date we need to update prices from.
Finally, we will call the yfinance API to return either a full time series, or a partial time series to append to the existing one, depending on which of the Except or Else code blocks ran.
Now for the self-updating part…
PythonAnywhere makes it relatively simple to schedule this type of task in the cloud. It’s also free! So go right ahead and handball this one to the virtual version of yourself and put your feet up!
The documentation is pretty easy to follow so I won’t make this a PythonAnywhere tutorial. Just create your free account, add a Python console along with a bash console to install the packages you need. Upload your script/s and then schedule your task to run at a specific time each day.
And that’s it. A low touch, self-updating database of prices ready at your fingertips for that next trading strategy that will make you millions!
Whilst this article was written with financial data in mind, it certainly has much broader use cases. As a side note, this is also a great example of how you can use the full Try, Except, Else and Finally, as opposed to just a Try and Except. Give it a go and let me know how you get on. Any feedback is welcome and appreciated!
