Django Models for Time Series Data

Question

My friends and I building an app that buy and sell stocks and we want to keep the historical prices of each stocks that we have in our possession by the end of day. The 3 most important fields are the ticker symbol and the price and the date.

For example:

01/01/2018 - Bought Stock A, record price of Stock A at end of day(EOD)
01/02/2018 - Did nothing, record price of Stock A at EOD
01/03/2018 - Bought Stock B, record price of Stock A and Stock B at EOD
01/04/2018 - Sell Stock A, record price of Stock B at EOD

We are using Django to build the models. Everyday we will record the price of each stock we have in our possession. This set of data is only for external use and will not be exposed to the public.

My initial research tells me it is not ideal to have a single table for historical prices and store each price for per stock as a single row. I'm not sure what the best approach is while using Django. What would the Django model to store all of this data look like and should we be using MYSQL?

score 6 · Accepted Answer · answered Jul 03 '18 at 13:09

You separate into 3 data models:

The model for the stock, having links to histories and current price and amount, as well as metadata
The model for the purchase history
The model for the price history

The three are linked with foreign keys from the stock model. Example code:

from django.db import models
from django.util.translation import ugettext_lazy as _
from datetime import date


class StockAsset(models.Model):
    symbol = models.CharField(max_length=5)
    amount = models.PositiveIntegerField()
    price = models.FloatField()

class PurchaseHistory(models.Model):
    BUY = 1
    SELL = 2
    ACTION_CHOICES = (
        (BUY, _('buy')),
        (SELL, _('sell')),
    )
    action = models.PositiveIntegerField(choices=ACTION_CHOICES)
    action_date = models.DateTimeField(auto_now_add=True)
    stock = models.ForeignKey(StockAsset,
        on_delete=models.CASCADE, related_name='purchases'
    )

class PriceHistory(models.Model):
    stock = models.ForeignKey(StockAsset, on_delete=models.CASCADE, related_name='price_history')
    price_date = models.DateField(default=date.today)

This way you can access all from the StockAsset model. Start reading here.

For this, the type of database to pick is not really important. If you have no preference, go with PostgreSQL.

Thank you for the answer, Melvyn! Here is my understanding, the `StockAsset` table will keep track of the most recent price and the quantity we have for said stock. `PurchaseHistory` table will keep track of all our transactions. `PriceHistory` will keep track of all the historical data at the EOD. Did I get that right? Do you know if this would work well with scale? Lets say we have 2000 stock holdings and we store 2000 prices each day. — Alan, Jul 03 '18 at 13:56
It scales just fine, as for most use cases, you're only creating at best two small table joins. — Melvyn Sopacua, Jul 04 '18 at 11:37
`price_date` is a date field so where do you store price history? — Florent, Jan 11 '20 at 08:37

score 1 · Answer 2 · answered Dec 23 '19 at 13:35

If you care only about date, and not timestamp of every tick of price change then django-simple-history is a way to go.

You just update value (a price) and saving it in time series in a different table is up to that library, not even need to define a date field.

class StockAsset(models.Model):
    history = HistoricalRecords()

    symbol = models.CharField(...)
    price = models.DecimalField(max_digit=8, decimal_places=2)

Django Models for Time Series Data

2 Answers2