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?

Answer 1

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.

Answer 2

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)