2

I will preface this with the fact that I am entirely new to development. I've thrown together a Google Addon within a couple months, but that's about it. (Just to show you my limited experience.)

I'm in the process of converting my "database" into an actual database. Currently, I'm using Google Sheets to store data on a rolling time frame. By rolling I mean data will save for X amount of time, and then old data will be deleted to make room for the new data. I'd prefer to have a legit database setup to avoid this because certain calculations I am making would work much better with more/older data over time to show a more evident trend.

I am currently using this method for a Google addon that deals with cryptocurrency. Specifically, I capture data from the exchanges (open,low,high,close etc) show the "real time" (quotes because there are several min delays between each call because of G sheet trigger/timer limits), show that data in a nice format to the user, and then also save that data on the "back end" in hourly/4 hour time frames to make calculations on it. (Many daisy chained sheets here)

I will say, even with the cell/data capping being an issue, and the fact that I have had to daisy chain so many sheets together for this to work, the fact that I can make calculations right there in a formula has been great.

So in one instance, Google Sheets has been very helpful in doing things I want to happen. But the data storage issue is starting to become a real problem.

Anyways, now that there's some context to the issue. I've never dealt with any real databases. I'm somewhat experienced working with code (copy & paste dev more or less) specifically with Python and JavaScript. Google Apps scripts is primarily JavaScript and that's where a lot of my experience comes from. But I'm definitely a real beginner here.

I'm looking for recommendations on a simple database that I can use to get this thing setup. I've done some research, and I'm still unsure what kind of DB would be in my best interest for this project. (Relational/non-relational?) I'm assuming some kind of time series DB because it deals with continuous "financial" data. Part of that data I basically just want to regurgitate back out from the exchanges (OHLC data) but just in a nice format. Other parts I need to capture, save, and then do calculations as time goes by (Avgs, Means, etc). I came across timescaleDB and was intrigued. But I thought it would be best to ask people who are more experienced in this area before I go down a rabbit hole.

So to sum up what I am currently doing and looking for:

Saving OHLC exchange data from (currently) 5 different exchanges. (This will most likely just be turned right around within the "app" I'm trying to build for real time data, but also needs to be saved for calculations as data is saved over time.) (Ps. Does this need to be relational? Is that better in this instance for querying data?)

I need to some how be able to make calculations on this data. These will be more than just simple Avg/Mean calculations, but those will be used also. (Nothing like super crazy, but still some power to it. I've read that it's best to have server side calculations?)

I'll need to be able to query the data by different scenarios to actually make those calculations. IE: Query all of BTC-ADA from Bittrex and get the OHLC candle data from dates X to X.

(Not sure if this is relevant or not) Setup webhooks/end points so users can query certain data for outside uses.

This is a long post, but I wanted to be detailed because I've been researching for quite a bit. I'm just lost. Thanks for any suggestions/help.

0

Given that you are working on timeseries data, I would suggest that you read through some of the links I have enclosed from the Quant Stackexchange forum to understand the options out there, and the reasoning for the decisions:

https://quant.stackexchange.com/questions/29572/building-financial-data-time-series-database-from-scratch

https://quant.stackexchange.com/questions/23210/which-database-to-choose-for-storing-and-aggregating-finance-data/

https://quant.stackexchange.com/questions/3156/is-there-any-thing-out-there-as-a-substitute-for-kdb

https://quant.stackexchange.com/search?tab=Relevance&pagesize=50&q=kdb

In-memory database for streaming market data

Some of the options vary from: KDB+- a popular options used by financial firms with a free version for non-commercial use; to Flatfile; Postgres; MariaDB with Column Store; etc.

  • 1
    Excellent, thank you for your time. – Phil Jan 30 at 21:15
0

Although you have already accepted an answer, this might help others in future (and you might also consider it, if you have not yet started implementation).

By rolling I mean data will save for X amount of time, and then old data will be deleted to make room for the new data.

Sounds like you could use a Round Robin Database

RRDtool (round-robin database tool) aims to handle time series data such as network bandwidth, temperatures or CPU load. The data is stored in a circular buffer based database, thus the system storage footprint remains constant over time.

It also includes tools to extract round-robin data in a graphical format, for which it was originally intended. Bindings exist for several programming languages, e.g. Perl, Python, Ruby, Tcl, PHP and Lua. There is an independent full Java implementation called rrd4j. 

That’s just one example. Google for Round Robin Database and you will find more.

Btw, since MySql is so popular, although it is not Round Robin, someone has found a way to make it (approximately) so : Round Robin Database with MySQL

  • 1
    Thanks for looking into this. But I'm working on trying to avoid the circular action that I'm dealing with now in Gsheets because of the data limit. Granted this could still be useful in certain scenarios. But I am looking to capture the data and store it more long term (I do indeed want it to grow and not stay constant) rather than having to delete it. Thanks anyways though! – Phil Feb 2 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.