Millet Porridge

English version of https://corvo.myseu.cn

0%

Using InfluxDB and Sharing Some A-Share Holdings

Posted on Edited on In Disqus:
Recently I've been collecting lots of personal data for statistics, including my long-time stock holdings. The data is stored with InfluxDB, and the frontend uses Grafana for permission control and page display. So I want to share some InfluxDB usage and common query statements. The content shared here constitutes no investment advice.

Recently I’ve been collecting lots of data around me for statistics, and reported my long-time stock holdings too. The data is stored using InfluxDB, and the frontend also uses Grafana for permission control and page display. So I want to share some InfluxDB usage and common query statements. The content shared in this article constitutes no investment advice.

Currently my Sinolink account holds about 170k, with a return rate around 10%. Certainly no comparison with the experts wielding millions and doubled returns.

I added a section to the blog for displaying Grafana charts — view it directly at Stocks

Data Sources

I’ve been playing in A-shares for several years; last year I discovered the QMT tool, which can interact with your own securities account using Python:

http://docs.thinktrader.net/pages/040ff7/#%E8%BF%85%E6%8A%95xtquant-faq

Enabling it is fairly simple — Sinolink’s threshold is 300k; just ask your account manager to enable it.

1721227030807.png

Last year I wrote my own tool for scheduled investments; recently while collecting data around me, I collected holdings and returns as well. There’s actually quite a lot of other data, but I feel my holdings are most suitable for charting and sharing — readers may also be more interested.

Below is the data-writing function. Writing this blog I noticed my code is a bit casual — as long as it runs for now.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# report the current account's overall data
p = Point("balance") \
    .tag('account', stock_account) \
    .field("total_asset", b.total_asset) \
    .field("market_value", b.market_value) \
    .time(dt.datetime.utcnow(), WritePrecision.S)
with client.write_api(write_options=SYNCHRONOUS) as write_api:
    write_api.write(bucket=bucket, record=p)

# report current holdings data
for s in stocks:
    p = Point("position") \
        .tag('account', stock_account) \
        .tag('stock_code', s.code) \
        .tag('stock_name', s.name) \
        .tag('market', s.market) \
        # current holding volume
        .field('volume', s.volume) \
        # current per-share price
        .field('volume_price', s.volume_price) \
        # current total holding price
        .field('market_price', s.market_price) \
        # per-share cost
        .field('open_price', s.open_price) \
        .field('position_ratio', round(s.market_price / b.market_value, 3)) \
        .time(dt.datetime.utcnow(), WritePrecision.S)

    with client.write_api(write_options=SYNCHRONOUS) as write_api:
        write_api.write(bucket=bucket, record=p)

Click the image to enlarge and view this example:

1721227802438.png

Some InfluxDB Query Statements Shared

While making charts I had the chance to query with InfluxDB, so I organized some statements I used. It can build custom functions — truly powerful; I hope sharing brings readers some inspiration.

Displaying Holdings and Totals

1
2
3
4
5
6
7
8
from(bucket: "Stock")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "balance")
  |> filter(fn: (r) => r["_field"] == "market_value")
  |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
  |> drop(columns: ["_field", "account", "stock_code", "market"])
  |> set(key: "_measurement", value: "持仓金额")
  |> yield(name: "last")

This is the most basic chart:

1721228735233.png

Computing the Overall Position Situation

1
2
3
4
5
6
7
8
9
10
11
12
from(bucket: "Stock")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "balance")
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ 
      _time: r._time,
      // the map function computes holdings amount / total amount — e.g. I have 170k, 160k in stocks, so the position is 16/17=0.94
      _value: r.market_value / r.total_asset,
    }))
  // drop some columns we don't want
  |> drop(columns: ["_field", "account", "stock_code", "market", "total_asset", "market_value"])
  |> set(key: "_measurement", value: "仓位")

InfluxDB source data:

1721228581634.png

In Grafana, you can make:

1721228609869.png

Computing My Actual Return Rate

This chart actually includes the amount I’ve invested — data that theoretically changes over time. For example, if I invested 15k this month, the actual return rate should be computed with the updated holdings data.

The scheme I can think of: add a function to augment a field whose meaning indicates the amount invested so far:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
getBase = (r) => {
  dtime = r._time
  data =
    if dtime > time(v: "2024-07-15T19:00:00Z") then
        160000.0
    else
        145000.0
  return data
}

from(bucket: "Stock")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "balance")
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ 
      _time: r._time,
      _measurement: r._measurement,
      base: getBase(r),
      // compute the actual return rate — e.g. I originally invested 160k and now have 170k, so the return rate is (17-16)/16 = 6%
      _value:  (r.total_asset - getBase(r)) / getBase(r),

    }))
  |> drop(columns: ["_field", "account", "stock_code", "market", "total_asset", "market_value", "base"])
  |> set(key: "_measurement", value: "收益率")

But I think this getBase function isn’t elegant enough, so here I give a lookup-table scheme:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
import "array"

inputVal = [
  {date: time(v: "2024-07-15T19:00:00Z"), val: 160000.0 },
  {date: time(v: "2024-01-01T19:00:00Z"), val: 145000.0 },
]

// the getBase function here looks up its own time period from the table and returns the corresponding invested amount.
// For example, I invested 15k on July 15, so data after July 15 becomes 160k.
// When I continue investing on some day in August, another row can be added to mark it.
getBase = (query) => {
  tdata = (
    inputVal |> array.filter(fn: (x) => {
      return query._time >= x.date
    })
  )
  return tdata[0].val
}

from(bucket: "Stock")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "balance")
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ 
      _time: r._time,
      _measurement: r._measurement,
      base: getBase(query: r),
      _value:  (r.total_asset - getBase(query:r)) / getBase(query:r),
    }))
  |> drop(columns: ["_field", "account", "stock_code", "market", "total_asset", "market_value", "base"])
  |> set(key: "_measurement", value: "收益率")

The actual return-rate curve is fairly smooth:

1721229433054.png

Additionally, here’s another idea: record each transfer amount and use reduce to compute the total

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
import "array"

// record each bank-to-securities transfer amount
inputVal = [
  {date: time(v: "2024-07-15T19:00:00Z"), val: 15000.0 },
  {date: time(v: "2024-01-01T19:00:00Z"), val: 145000.0 },
]

getBase = (query) => {
  tdata = (
   array.from(rows:inputVal) |> reduce(
    identity: {totalInput: 0.0},
    fn: (r, accumulator) => ({
      totalInput: if query._time > r.date then
        accumulator.totalInput + r.val
      else
        accumulator.totalInput
    }))
    |> yield()
    |> findRecord(fn:(key) => true, idx: 0)
  )
  return tdata.totalInput
}

Per-Stock Holdings Distribution

This one is simple — just make a pie chart by market_price:

1
2
3
4
5
6
7
from(bucket: "Stock")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "position")
  |> filter(fn: (r) => r["_field"] == "market_price")
  |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
  |> drop(columns: ["_field", "account", "stock_code", "market"])
  |> yield(name: "last")

1721229745591.png

Per-Stock Return Rates

1
2
3
4
5
6
7
8
9
10
11
12
from(bucket: "Stock")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "position")
  |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with
      _time: r._time,
      _measurement: r._measurement,
      // using current (price - cost) / cost gives a single stock's return rate
       _value: (r["volume_price"] - r["open_price"]) / r["open_price"],
  }))
  |>group(columns: ["stock_name"])
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)

1721229616399.png

Summary

Someone will surely ask why I share holdings data for free. What I want to say: even after seeing my position distribution, you most likely won’t buy these stocks of mine. Here’s why:

  1. Theory is ignored entirely — just as with the Kelly formula already existing, people still stay fully invested forever, forever teary-eyed
  2. My 10% return is beneath their notice
  3. The many existing paid consultation groups — lots of stock investors trust group members’ judgments or inside information more

Still, let me emphasize once more: this article only shares technology and constitutes no investment advice.

Related Posts