Algorithm for aggregating stock chart datapoints out of many DB entries

Question

I have a database with stock values in a table, for example:

id - unique id for this entry
stockId - ticker symbol of the stock 
value - price of the stock
timestamp - timestamp of that price

I would like to create separate arrays for a timeframe of 24 hour, 7 days and 1 month from my database entries, each array containing datapoints for a stock chart. For some stockIds, I have just a few data points per hour, for others it could be hundreds or thousands.

My question: What is a good algorithm to "aggregate" the possibly many datapoints into a few - for example, for the 24 hours chart I would like to have at a maximum 10 datapoints per hour. How do I handle exceptionally high / low values?

What is the common approach in regards to stock charts?

Thank you for reading!

Answer 1

Some options: (assuming 10 points per hour, i.e. one roughly every 6 minutes)

• For every 6 minute period, pick the data point closest to the centre of the period

• For every 6 minute period, take the average of the points over that period

• For an hour period, find the maximum and minimum for each 4 minutes period and pick the 5 maximum and 5 minimum in these respective sets (4 minutes is somewhat randomly chosen).

  I originally thought to pick the 5 minimum points and the 5 maximum points such that each maximum point is at least 8 minutes apart, and similarly for minimum points.

  The 8 minutes here is so we don't have all the points stacked up on each other. Why 8 minutes? At an even distribution, 60/5 = 12 minutes, so just moving a bit away from that gives us 8 minutes.

  But, in terms of the implementation, the 4 minutes approach will be much simpler and should give similar results.

You'll have to see which one gives you the most desirable results. The last one is likely to give a decent indication of variation across the period, whereas the second one is likely to have a more stable graph. The first one can be a bit more erratic.