北向分钟级资金流向与大盘指数的相关性分析

流光 · 发表于 2023-12-28 16:54:26

随着沪深港通合作的不断深入，无论你是否在意，北向资金都已经成为影响大盘走势的一个重要因素。本文借助讯投QMT提供的北向分钟级资金流向历史数据，分析其与大盘的相关性，希望能和读者一起挖掘其中的可利用点。

一、历史数据下载和存储
1.1 数据下载

这里我们通过xtquant库下载北向资金流向历史数据和大盘指数数据, 首先建立环境：

# -*- coding: utf-8 -*-

import datetime, time
import pandas as pd
from xtquant import xtdatacenter as xtdc
import xtquant.xtdata as xtdata
from clickhouse_driver import Client
from tqdm import tqdm
xtdc.set_token('3e6c******5501')
xtdc.set_data_home_dir('/data/qmt')

try:
xtdc.init()
except:
time.sleep(3)
xtdc.init()

xtdata.reconnect('127.0.0.1', 58609)
db_client = Client('**.**.**.**', port=**, password='******', settings={'use_numpy': False})

然后下载历史数据：

xtdata.download_history_data('FFFFFF.SGT', 'northfinancechange1m', '', '')
xtdata.download_history_data2(['000001.SH', '399001.SZ', '000016.SH', '000300.SH', '000852.SH', '000905.SH', '399006.SZ', '000688.SH', '399330.SZ'], '1m' , '', '')
1.2 北向资金数据存储至数据库

建立Clickhouse数据库表,其中HGT表示沪股通，SGT表示深股通：

create table if not exists qmt.northfinancechange1m
(
  TradeDate Date,
  TradeTime DateTime('Asia/Shanghai'),
  TradeMinute UInt16,
  HGTNorthBuy Int64 DEFAULT 0 COMMENT 'HGT北向买入资金',
  HGTNorthSell Int64 DEFAULT 0 COMMENT 'HGT北向卖出资金',
  HGTSouthBuy Int64 DEFAULT 0 COMMENT 'HGT南向买入资金',
  HGTSouthSell Int64 DEFAULT 0 COMMENT 'HGT南向卖出资金',
  SGTNorthBuy Int64 DEFAULT 0 COMMENT 'SGT北向买入资金',
  SGTNorthSell Int64 DEFAULT 0 COMMENT 'SGT北向卖出资金',
  SGTSouthBuy Int64 DEFAULT 0 COMMENT 'SGT南向买入资金',
  SGTSouthSell Int64 DEFAULT 0 COMMENT 'SGT南向卖出资金',
  HGTNorthFlow Int64 DEFAULT 0 COMMENT 'HGT北向资金净流入',
  HGTNorthBalance Int64 DEFAULT 0 COMMENT 'HGT北向当日资金余额',
  HGTSouthFlow Int64 DEFAULT 0 COMMENT 'HGT南向资金净流入',
  HGTSouthBalance Int64 DEFAULT 0 COMMENT 'HGT南向当日资金余额',
  SGTNorthFlow Int64 DEFAULT 0 COMMENT 'SGT北向资金净流入',
  SGTNorthBalance Int64 DEFAULT 0 COMMENT 'SGT北向当日资金余额',
  SGTSouthFlow Int64 DEFAULT 0 COMMENT 'SGT南向资金净流入',
  SGTSouthBalance Int64 DEFAULT 0 COMMENT 'SGT南向当日资金余额'
)
ENGINE = ReplacingMergeTree()
ORDER BY (TradeDate, TradeTime)

写入数据：

data = xtdata.get_market_data_ex([], ['FFFFFF.SGT'], 'northfinancechange1m', '', '')['FFFFFF.SGT']
data['time'] = pd.to_datetime(data.time, unit='ms') + pd.Timedelta(hours=8)
data['TradeMinute'] = data.time.dt.hour * 60 + data.time.dt.minute
data['TradeDate'] = data.time.dt.date

data = data.rename(columns={'time' : 'TradeTime', 'HGT北向买入资金': 'HGTNorthBuy', 'HGT北向卖出资金': 'HGTNorthSell', 'HGT南向买入资金': 'HGTSouthBuy', 'HGT南向卖出资金': 'HGTSouthSell', 'SGT北向买入资金': 'SGTNorthBuy', 'SGT北向卖出资金': 'SGTNorthSell', 'SGT南向买入资金': 'SGTSouthBuy', 'SGT南向卖出资金': 'SGTSouthSell', 'HGT北向资金净流入': 'HGTNorthFlow', 'HGT北向当日资金余额': 'HGTNorthBalance', 'HGT南向资金净流入': 'HGTSouthFlow', 'HGT南向当日资金余额': 'HGTSouthBalance', 'SGT北向资金净流入': 'SGTNorthFlow', 'SGT北向当日资金余额': 'SGTNorthBalance', 'SGT南向资金净流入': 'SGTSouthFlow', 'SGT南向当日资金余额': 'SGTSouthBalance'})

db_client.insert_dataframe('INSERT INTO qmt.northfinancechange1m VALUES', data, settings={'use_numpy': True})
1.3 指数数据存储至数据库

建立数据表：

create table if not exists qmt.mainindex1m
(
  TradeDate Date,
  TradeTime DateTime('Asia/Shanghai'),
  TradeMinute UInt16,
  IndexCode LowCardinality(String),
  open Float32 DEFAULT 0,
  close Float32 DEFAULT 0,
  high Float32 DEFAULT 0,
  low Float32 DEFAULT 0,
  volume Int64 DEFAULT 0,
  amount Float64 DEFAULT 0,
  preClose Float32 DEFAULT 0
)
ENGINE = ReplacingMergeTree()
ORDER BY (TradeDate, TradeTime, IndexCode)

写入数据:

index_tickers = ['000001.SH', '399001.SZ', '000016.SH', '000300.SH', '000852.SH', '000905.SH', '399006.SZ', '000688.SH', '399330.SZ']

for index in index_tickers:
      print(index)
      data2 = xtdata.get_market_data_ex([], [index], '1m', '', '')[index]
      data2['time'] = pd.to_datetime(data2.time, unit='ms') + pd.Timedelta(hours=8)
      data2['TradeMinute'] = data2.time.dt.hour * 60 + data2.time.dt.minute
      data2['TradeDate'] = data2.time.dt.date
      data2['IndexCode'] = index
      data2 = data2.rename(columns={'time' : 'TradeTime'})

      db_client.insert_dataframe('INSERT INTO qmt.mainindex1m  VALUES', data2[['TradeDate', 'TradeTime', 'TradeMinute', 'IndexCode', 'open', 'high', 'low', 'close', 'volume', 'amount', 'preClose']], settings={'use_numpy': True})

二、北向资金净流入与日内指数表现分析
2.1 日内收益计算

利用with as语法，事先计算指数的5分钟、10分钟内收益

with index_intraday_return as
(
  select
TradeDate, IndexCode, TradeMinute, Next2HighRate, Next2LowRate,  Next5HighRate, Next5LowRate, Next10HighRate, Next10LowRate
  from (
select
      TradeDate, IndexCode, TradeMinute,
      max(high) OVER (PARTITION BY TradeDate, IndexCode ORDER BY TradeMinute asc ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) as Next2High,
            min(low) OVER (PARTITION BY TradeDate, IndexCode ORDER BY TradeMinute asc ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) as Next2Low,
            max(high) OVER (PARTITION BY TradeDate, IndexCode ORDER BY TradeMinute asc ROWS BETWEEN 1 FOLLOWING AND 5 FOLLOWING) as Next5High,
            min(low) OVER (PARTITION BY TradeDate, IndexCode ORDER BY TradeMinute asc ROWS BETWEEN 1 FOLLOWING AND 5 FOLLOWING) as Next5Low,
            max(high) OVER (PARTITION BY TradeDate, IndexCode ORDER BY TradeMinute asc ROWS BETWEEN 1 FOLLOWING AND 10 FOLLOWING) as Next10High,
            min(low) OVER (PARTITION BY TradeDate, IndexCode ORDER BY TradeMinute asc ROWS BETWEEN 1 FOLLOWING AND 10 FOLLOWING) as Next10Low,
         round(1000 * (Next2High / close - 1), 2) as Next2HighRate,
         round(1000 * (Next2Low / close - 1), 2) as Next2LowRate,
         round(1000 * (Next5High / close - 1), 2) as Next5HighRate,
         round(1000 * (Next5Low / close - 1), 2) as Next5LowRate,
         round(1000 * (Next10High / close - 1), 2) as Next10HighRate,
         round(1000 * (Next10Low / close - 1), 2) as Next10LowRate
from
      qmt.mainindex1m
where
   TradeDate >= '2023-06-01'
  )
  where TradeMinute >= 9*60 + 31 and TradeMinute <= 14 * 60 + 55
),
2.2 北向资金净流入因子提取

这里简单提取分钟级资金流入差作为因子：

north_flow_factor as
(
select
      TradeDate, TradeMinute, SGTNorthFlow, HGTNorthFlow,
      SGTNorthFlow - any(SGTNorthFlow) OVER (PARTITION BY TradeDate ORDER BY TradeMinute asc ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) as Prev1SGTNorthFlow,
      SGTNorthFlow - any(SGTNorthFlow) OVER (PARTITION BY TradeDate ORDER BY TradeMinute asc ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) as Prev5SGTNorthFlow,
      SGTNorthFlow - any(SGTNorthFlow) OVER (PARTITION BY TradeDate ORDER BY TradeMinute asc ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING) as Prev10SGTNorthFlow,
      HGTNorthFlow - any(HGTNorthFlow) OVER (PARTITION BY TradeDate ORDER BY TradeMinute asc ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) as Prev1HGTNorthFlow,
      HGTNorthFlow - any(HGTNorthFlow) OVER (PARTITION BY TradeDate ORDER BY TradeMinute asc ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) as Prev5HGTNorthFlow,
      HGTNorthFlow - any(HGTNorthFlow) OVER (PARTITION BY TradeDate ORDER BY TradeMinute asc ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING) as Prev10HGTNorthFlow
from
      qmt.northfinancechange1m
where
   TradeDate >= '2023-06-01' and
   ((TradeMinute >= 9*60 + 30 and TradeMinute <= 11*60 + 30) or (TradeMinute >= 13*60 and TradeMinute <= 15*60))
)
2.3 相关性分析

计算因子和收益的相关性

select
round(corr(Prev1HGTNorthFlow, Next2HighRate), 3) as HPrev1Next2HighCorr,
round(corr(Prev1HGTNorthFlow, Next5HighRate), 3) as HPrev1Next5HighCorr,
round(corr(Prev5HGTNorthFlow, Next2HighRate), 3) as HPrev5Next2HighCorr,
round(corr(Prev5HGTNorthFlow, Next5HighRate), 3) as HPrev5Next5HighCorr,
round(corr(Prev10HGTNorthFlow, Next2HighRate), 3) as HPrev10Next2HighCorr,
round(corr(Prev10HGTNorthFlow, Next5HighRate), 3) as HPrev10Next5HighCorr,
round(corr(Prev1HGTNorthFlow, Next2LowRate), 3) as HPrev1Next2LowCorr,
round(corr(Prev1HGTNorthFlow, Next5LowRate), 3) as HPrev1Next5LowCorr,
round(corr(Prev5HGTNorthFlow, Next2LowRate), 3) as HPrev5Next2LowCorr,
round(corr(Prev5HGTNorthFlow, Next5LowRate), 3) as HPrev5Next5LowCorr,
round(corr(Prev10HGTNorthFlow, Next2LowRate), 3) as HPrev10Next2LowCorr,
round(corr(Prev10HGTNorthFlow, Next5LowRate), 3) as HPrev10Next5LowCorr,
round(corr(Prev1SGTNorthFlow, Next2HighRate), 3) as SPrev1Next2HighCorr,
round(corr(Prev1SGTNorthFlow, Next5HighRate), 3) as SPrev1Next5HighCorr,
round(corr(Prev5SGTNorthFlow, Next2HighRate), 3) as SPrev5Next2HighCorr,
round(corr(Prev5SGTNorthFlow, Next5HighRate), 3) as SPrev5Next5HighCorr,
round(corr(Prev10SGTNorthFlow, Next2HighRate), 3) as SPrev10Next2HighCorr,
round(corr(Prev10SGTNorthFlow, Next5HighRate), 3) as SPrev10Next5HighCorr,
round(corr(Prev1SGTNorthFlow, Next2LowRate), 3) as SPrev1Next2LowCorr,
round(corr(Prev1SGTNorthFlow, Next5LowRate), 3) as SPrev1Next5LowCorr,
round(corr(Prev5SGTNorthFlow, Next2LowRate), 3) as SPrev5Next2LowCorr,
round(corr(Prev5SGTNorthFlow, Next5LowRate), 3) as SPrev5Next5LowCorr,
round(corr(Prev10SGTNorthFlow, Next2LowRate), 3) as SPrev10Next2LowCorr,
round(corr(Prev10SGTNorthFlow, Next5LowRate), 3) as SPrev10Next5LowCorr
from (
  select TradeDate, TradeMinute, Next2HighRate, Next2LowRate, Next5HighRate, Next5LowRate, Next10HighRate, Next10LowRate
  from index_intraday_return
  where  TradeDate >= '2023-07-01' and TradeDate<'2023-08-01' and IndexCode='000016.SH'
) a
inner join (
  select TradeDate, TradeMinute, Prev1SGTNorthFlow, Prev5SGTNorthFlow, Prev10SGTNorthFlow, Prev1HGTNorthFlow, Prev5HGTNorthFlow, Prev10HGTNorthFlow
  from north_flow_factor
) b
on a.TradeDate = b.TradeDate and a.TradeMinute = b.TradeMinute

我们分别计算沪深北向资金与未来2/5分钟指数收益的相关系数，并按照月份和指数分别统计：

指数北向相关性.jpg