Stock Tracking Part 1

Posted by Brad on Mon 18 July 2022

I've been "normal" investing for a while, and thinking of becoming more active with it. Ideally, I would create some sort of bot and just give it a set amount of money to make trades. In reality, I'll probably settle for something that gives me a hint when I should buy or sell. Obviously, I'm probably not going to come up with some groundbreaking algorithm, but I believe I can at least beat some of the long term investments I've made. That said this is more about the learning part than actually making money. It's something to get me away from normal work projects while still being technical.

Starting Out

The obvious starting point is getting the data. Looking around it looks like there are a few APIs that provide free access with rate limits. I'm starting out with alphavantage, but I might move to something else that can do trades as well. Side note, it's been super interesting seeing some of the highest recommended websites look almost scammy because they aren't designed with that modern polish that I'm used to seeing on all the new shiny devops tools. Instead its just packed with a whole bunch of financial jargon, the more you can fit on the page the better. In any case I have a source of data, now I just have to pull the data and start using it.

To start out I have basically a top 5 list of stocks that I routinely check prices on. Normally that's just a google search, but if I stored the data I could use it for multiple things. I think I could integrate something into Home Assistant (and honestly I was suprised I didn't find something for it already). So I essentially just need some daily historical data and maybe do 5 minute intervals for the current day. I should be able to easily keep it within any rate limiting the API might have. But where do I store it? I'm going with postgresql because it's something I'm near daily but haven't actually done much with.

Databases: I Have No Idea What I'm Doing

So I'm going to cram this stock data into postgresql somehow, but I have no idea what a "correct" way looks like. I started looking at timeseries stuff and that seemed to suggest a table per time period, but really I don't care about performance and I'm not streaming the data (yet). So looking around some more I found this on stackoverflow and it seems like it might be reasonable. A third option I was thinking of is making a table per stock symbol. That actually might still be the way I go just to make it feel more organized.

Random side note: I love fedora's toolbox project, but it is beyond frustrating that I can't start new podman containers from inside the toolbox.


So I'll probably be a little lazy and just have grafana query the database directly instead of trying to make some sort of web frontend. The learning curve to get the queries working was pretty low and this is exactly the type of thing I've been looking to do with grafana for a while.

Putting It All Together

Doing a lot of POC code in a Jupyter notebook. The allure of docker-compose also makes a lot of sense here. Like to start out I want a postgres container, but I'll also end up wanting one for grafana and one to actually run the python code I write.

You can find code for the project here. The jupyter notebook did wonders for the learning part, I was able to split up sections as I made progress and it just kept everything nicely contained until I had enough of an idea to start writing actual functions.

Next up is adding some throttling to deal with the API rate limits. Then I need to fill out the data a bit more. And then it'll be time to relearn some of the math I forgot from college.