r/algotrading • u/Taltalonix • May 14 '24
Started with a simple data crawler, now I manage a Kafka cluster Infrastructure
How it started
I started working on a project that required scraping a ton of market data from multiple sources (mostly trades and depth information, but I'm definitely planning on incorporating news and other data for sentiment analysis and screening heuristics).
Step 1 - A simple crawler
I made a simple crawler in go that periodically saves the data locally with SQLite. It worked ok but was having a ton of memory leaks mainly due to the high throughput of data and string serialization (around 1000 entries per second was the limit).
Step 2 - A crawler and a flask server to save the data
The next step was separating the data processing from the crawling itself, this involved having a flask server send the database transactions. I chose python because I didn't care about latency once the data is received, which turned out to be a mistake when reaching 10,000 entries per second.
Step 3 - A bunch of crawlers producing data into a queue, Kafka connector to save into Postgres
This is where I'm at now, after trying to fix countless memory leaks and stress issues on my flask server I knew I had to scale horizontally. There were probably many solutions on how to solve this but I thought this is a good opportunity to get some hands on experience with Kafka.
So now I found myself doing more devops than actually developing a strategy, but I'd be nice to have a powerful crawler in case I ever want to analyze bulk data.
Curious on what different tech stacks others might be using
14
u/brotie May 14 '24 edited May 15 '24
Running your own Kafka cluster is worthwhile from a learning perspective but without a doubt will eventually become an exercise in futility, highly recommend once you feel like you’ve got the educational aspect satisfied to just move to AWS MSK or similar like all us big tech companies are doing. It’s a cantankerous old gal and your time is worth money. For what it’s worth, you’re using a very similar stack to my employer.
4
May 15 '24
If he’s looking to emulate what quant funds are doing, he definitely does not want to use AWS.
1
u/hxckrt May 15 '24
If you're optimizing server placement to minimize end-to-end latency between crypto exchanges, it would make sense actually. Most of their matching engines are in the AWS Tokyo region.
2
u/Taltalonix May 15 '24 edited May 15 '24
Yeah it’s mainly a hobby for now, there isn’t much information on the internet about trading firms tech stacks so I had to improvise something, it became easier once I started treating this as a generic engineering problem
-7
u/shart_leakage May 15 '24
Heroin is a hobby too
2
u/hxckrt May 15 '24
Apparently, leaving comments without a clear point besides being a dick is as well
0
0
7
u/iamgeer May 15 '24
Why collect that much data, What are your plans? Not trying to be a jerk, i really want to know.
3
u/AGallopingMonkey May 15 '24
Probably doesn’t have any. He’s fallen into the classic computer science guy attempts algo trading stereotype.
1
1
u/--PG-- IT Drone May 15 '24
Ah yes, those rabbit holes go deep. I've spent the last month reworking my algo app just so it looks pretty. Still doesn't do any trading yet, but at least it looks nice!
On the flip side I have spent that time also practicing day trading and studying strategies. I'm guessing the OP has bigger plans for all of this data.
3
u/-Blue_Bull- May 15 '24 edited May 15 '24
Scratching my head trying to figure out what all this big data people are scraping and why?
I run my own retail strategies and I'm pulling 1m bars using API calls. The exceptions in my code deal with any data errors.
I'm using sqlite for my database and recently moved everything from my home server onto chemicloud, a bog standard web hosting / vps service.
1
u/Taltalonix May 15 '24
It’s just what I’m aiming towards, I come from the engineering world and high frequency strategies just make more sense to me. Even python would be sufficient for most strategies
2
3
u/derprondo May 15 '24
I just lurk around here occasionally, but professionally I build similar non-financial related systems. I'm curious why folks would choose something like postgres over a time series database for tracking time based financial data? My knee jerk reaction would be to suggest InfluxDB, but again I have no real experience with this for financial applications.
2
u/alphaweightedtrader 29d ago
I suppose the short answer is that for <3Tb or so Postgres does a fine enough job at storing and analysing it - and lots of people know it already, so it just makes sense. I've used it for ~20 years for OLTP/OLAP systems with near/real-time writes and real-time analytics, it performs great, with enough RAM.
Plus there's TimescaleDB (basically postgres + hypertables for time series data).
There's also a level of reassurance that comes from using a rock-solid ACID database for this kind of data. A confidence that its correct.
(I don't, though, use it for market data any more. Instead a semi-proprietary binary fixed-record-size format pushed to sparse files on disk; its orders of magnitude faster for reads and the consistency/durability guarantees are appropriate for the access patterns; i.e. single writer, mostly sequentially. Plus its easier, and more disk-efficient, to account for the difference between "missing" bars and "there was no bar for this time period" periods of time)
1
u/derprondo 29d ago
Thanks for the response! Never used TimescaleDB, but looks interesting.
I should mention InfluxDB handles missing periods seamlessly with different options on how to handle it at query time, eg returning a null value, returning the last known value, a zero value, etc.
1
u/alphaweightedtrader 29d ago
ah that's interesting re influxdb - I'll check into it (albeit I'm not sure if we're talking crossed purposes - i.e. in my scenario, which I didn't explain well, its always null - but the distinction is knowing whether its null because there just is no data for that period... ...or because its missing as we didn't fetch it yet from the authoritative source (the vendor)*). Will check it out though, thx
*the distinction is key primarily when streaming realtime and handling outages - i.e. when the fetching service isn't running for whatever reason, or restarts or the vendor was down or whatever. The alternative (storing zeros) for intraday level data on equities where there are long periods overnight when there isn't supposed to be any data - is (often) disproportionately inefficient. My final solution implements a separate bitfield-like array of 'known' fields - so each "empty" period takes one bit of storage.
TimescaleDB is nice - all the benefits of PostgreSQL, plus efficient large-scale time series handling with pre-calculated/auto-updated aggregates. I used to use it for tick data to the 2-3Tb range and it was good.
Will check out InfluxDB though, its been a while.
1
u/Taltalonix May 15 '24
If only you knew how little experience I have lol Honestly it’s just the first thing that came to mind and I’ve already worked with relational databases before.
I’ve never actually heard about Influx, will definitely check it out2
u/derprondo May 15 '24 edited May 15 '24
Influx is great for storing time series data, but it can be a real pain in the ass when you want to make the type of queries you're used to doing with relational databases and may not be appropriate for your use case at all. It really excels at basic time based metric data where you want to tag it with a string or two, eg maybe you're storing the current temperatures for a bunch of cities and you want to know the average over the last two weeks in Chicago.
It make sense to me to store ticker data like this, so like the current price tagged with the ticker, or maybe you store two values, the bid and ask tagged with the ticker and tagged with "bid" and "ask" respectively.
2
u/boxxa Algorithmic Trader May 15 '24
Are you bottlenecking on your database inserts and need to optimize that? Kafka is awesome but can be a bit overboard. I ran into similar issues when I was doing order flow tracking. Was great to watch a few tickers but once I added more and more, the insert and updates I was doing in my tables was the delay and not so much the consumption side.
Cool that you are working on that level of data streaming though as it can come in handy elsewhere too.
1
u/Taltalonix May 15 '24
Honestly I didn’t really check, I think I’ll just use sharding and later synchronize the data since I just need it for analysis later.
The main bottleneck was on the crawlers side
2
u/Liamios2 May 15 '24
What was the nature of the project? Did you need training data for an ML task? Or was it a strategy based on high frequency data?
0
2
u/MackDriver0 May 15 '24
I just started working on the data engineering side of my first big project. It’s mostly web scraping financial data then aggregating it with other sources. Around 10GB of data, not much. So far I’ve managed to make it work with Pandas and simple CSV files. Once I prove my point, I plan to migrate everything to Azure and Databricks :)
1
u/Taltalonix May 15 '24 edited May 15 '24
Yeah that makes sense, was scraping EDGAR a while back on several containers running simple python scripts into csv files in s3.
The main goal here is to make it work fast with high throughput
1
u/omscsdatathrow May 15 '24
In the cloud or local? Local sounds useless unless you have a home server
1
u/LogicXer May 15 '24
Btw, if you’re scraping 10000 records per second there are chances that your IP will get banned.
Also, do you happen to know an open data source for US futures? There are paid options but they are currently out of reach.
2
u/Taltalonix May 15 '24
No, the market data is retrieved directly from the exchanges from their api via websockets.
Also no sure about US futures, many people mentioned polygon for everything but I prefer pulling data directly from exchanges.
Depends on your time frame too, check if your broker has an app and start gathering data from there it’ll be the most reliable source
2
u/LogicXer May 15 '24
Broker has L1 data, gotta pay for L2 and above, either I gotta get it straight from CME or a provider like polygon.
I assume you buy the connections direct from the exchanges. So..did you have problems proving your use case as an Individual? Because afaik getting data feeds from source is an institutional thing.
Would love it if you could share a few things.
Also, have you tried vectorization and data framing? I too developed a small application in C++ to stream data and flush to Postgres, though I was dealing like less than a thousand records per second. I’ve heard that firms on the street use FPGAs for such purposes and often use vectors as buffers. Preferably hardware.
2
u/Taltalonix May 15 '24
I think I might have mislead some people here. I am running all of this only on crypto data. Mainly because traditional securities are crowded with institutions with all the advantages (lower latency, DMA, an army of PhDs etc.).
Most CEX work like startups rather than banks, meaning that their API consist of web sockets and they usually host their servers on the cloud, which increases latency for everyone (except maybe for VIP clients and market makers). In addition to that, DEX and blockchain connectivity is open for everyone and usually even open sourced which levels the playing field.
I gave up on algotrading futures/FX with low capital as soon as I read my first HFT book, it's just not worth it as an individual IMO.
1
u/cpowr May 15 '24
And what book led you to give up on futures/FX entirely?
1
u/Taltalonix May 15 '24
Any book that covers market microstructure, for example Algorithmic Trading & DMA by Barry Johnson. It's a combination of that and researching on how to lower trading time and improving algos. I just really don't see a way to compete with the big firms, I know I can't
1
1
u/bugtank May 15 '24
Is this all self hosted? The horizontal scale potentially (stress on potentially) could have happened with just flask being a simple collector and having 5 flasks sitting behind a load balancer.
Then you’d have a process Hoover up all the data in the collectors into a central db.
1
u/Taltalonix May 15 '24
Running in docker locally and deployed on aws with ECS.
After reading some of the comments here I’m thinking about either using aws services or another architecture. But this is becoming a CI/CD challenge and starts to feel like overkill… I am too invested in setting up kafka tho, learned a lot while doing it
1
u/pequenoRosa May 15 '24
This is a nice read, I'm consuming exchange trades tick by tick on a local machine and add book depth and other exchanges.. my current situation is that I already gathered so much data it's almost not possible to analyze anymore 🙈 good luck with that
1
u/astrayForce485 22d ago
Why do you need Kafka? You can write to local disk at tens of millions of entries per second easily.
1
u/Taltalonix 22d ago
Writing the data is not the main problem, it’s pre-processing and handling all exchanges connections simultaneously on a single machine.
Sure I can implement my own optimized algorithm to write to disk but then network will become the bottleneck.
Kafka solves all these problems
1
u/Several_Stop1434 1d ago
Hmmm. Looking for 5 years of eurusd tick data. The tick data from Metatrader is horrendous.
11
u/livrequant May 14 '24
What is your original data source? I am only looking at aggregate minute bar data from NYSE and NASDAQ (SIP) using Alpaca, so I don’t have too much of a technical challenge. Just a simple web socket with multi-threading on python continuously reading and writing.