dataPuller.dos 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481
  1. module fundit::dataPuller
  2. use fundit::sqlUtilities
  3. /*
  4. * 取指数周收益
  5. *
  6. * get_index_weekly_rets("'FA00000WKG','FA00000WKH','IN0000007G'", 1990.01.01, today())
  7. */
  8. def get_index_weekly_rets(index_ids, start_date, end_date) {
  9. s_start_date = iif(start_date.isNull(), "", " AND price_date >= '" + start_date$STRING + "'")
  10. s_end_date = iif(end_date.isNull(), "", " AND price_date <= '" + end_date$STRING + "'")
  11. s_query = "SELECT factor_id AS index_id, year_week, price_date, factor_value AS cumulative_nav, ret_1w
  12. FROM pfdb.cm_factor_performance_weekly
  13. WHERE isvalid = 1
  14. AND factor_id IN (" + index_ids + ")" +
  15. s_start_date +
  16. s_end_date + "
  17. AND ret_1w IS NOT NULL
  18. UNION
  19. SELECT fund_id AS index_id, year_week, price_date, cumulative_nav, ret_1w
  20. FROM mfdb.fund_performance_weekly
  21. WHERE isvalid = 1
  22. AND fund_id IN (" + index_ids + ")" +
  23. s_start_date +
  24. s_end_date + "
  25. AND ret_1w IS NOT NULL
  26. ORDER BY year_week"
  27. conn = connect_mysql()
  28. t = odbc::query(conn, s_query)
  29. conn.close()
  30. return t
  31. }
  32. /*
  33. * 取基金周收益
  34. *
  35. *
  36. * get_fund_weekly_rets("'MF00003TMH','MF00003UQM'", 1990.01.01, null, true)
  37. */
  38. def get_fund_weekly_rets(fund_ids, start_date, end_date, isFromMySQL) {
  39. s_fund_id = iif(fund_ids.isNull(), "", " AND fund_id IN (" + fund_ids + ")")
  40. s_start_date = iif(start_date.isNull(), "", " AND price_date >= '" + start_date$STRING + "'")
  41. s_end_date = iif(end_date.isNull(), "", " AND price_date <= '" + end_date$STRING + "'")
  42. s_query = "SELECT fund_id, year_week, price_date, cumulative_nav, ret_1w
  43. FROM mfdb.fund_performance_weekly
  44. WHERE isvalid = 1 " +
  45. s_fund_id +
  46. s_start_date +
  47. s_end_date + "
  48. AND ret_1w IS NOT NULL
  49. ORDER BY fund_id, year_week"
  50. conn = connect_mysql()
  51. t = odbc::query(conn, s_query)
  52. conn.close()
  53. return t
  54. }
  55. /*
  56. * 取组合周收益
  57. * TODO: 增加从本地取数据的功能
  58. *
  59. *
  60. * get_portfolio_weekly_rets("166002,364640", 1990.01.01, today(), true)
  61. */
  62. def get_portfolio_weekly_rets(portfolio_ids, start_date, end_date, isFromMySQL) {
  63. s_portfolio_id = iif(portfolio_ids.isNull(), "", " AND portfolio_id IN (" + portfolio_ids + ")")
  64. s_query = "SELECT portfolio_id, year_week, price_date, cumulative_nav, ret_1w
  65. FROM pfdb.pf_portfolio_performance_weekly
  66. WHERE isvalid = 1 " +
  67. s_portfolio_id + "
  68. AND ret_1w IS NOT NULL
  69. AND price_date BETWEEN '" + start_date$STRING + "' AND '" + end_date$STRING + "'
  70. ORDER BY portfolio_id, year_week"
  71. conn = connect_mysql()
  72. t = odbc::query(conn, s_query)
  73. conn.close()
  74. return t
  75. }
  76. def get_monthly_ret(entity_type, entity_ids, start_date, end_date, isFromMySQL) {
  77. s_entity_ids = '';
  78. // 判断输入的 fund_ids 是字符串标量还是向量
  79. if ( entity_ids.form() == 0 ) {
  80. s_entity_ids = entity_ids;
  81. } else {
  82. s_entity_ids = "'" + entity_ids.concat("','") + "'";
  83. }
  84. tmp = get_performance_table_description(entity_type);
  85. yyyymm_start = start_date.temporalFormat("yyyy-MM")
  86. yyyymm_end = end_date.temporalFormat("yyyy-MM")
  87. if(isFromMySQL == true) {
  88. s_query = "SELECT " + tmp.sec_id_col[0] + ", end_date, price_date, ret_1m AS ret, " + tmp.cumulative_nav_col[0] + " AS nav, ret_ytd_a, ret_incep_a
  89. FROM " + tmp.table_name[0] + "
  90. WHERE " + tmp.sec_id_col[0] + " IN (" + s_entity_ids + ")
  91. AND isvalid = 1
  92. AND end_date BETWEEN '" + yyyymm_start + "' AND '" + yyyymm_end + "'
  93. ORDER BY " + tmp.sec_id_col[0] + ", end_date";
  94. conn = connect_mysql()
  95. t = odbc::query(conn, s_query)
  96. conn.close()
  97. } else {
  98. tb_local = load_table_from_local("fundit", tmp.table_name[0])
  99. s_col = (sqlCol(tmp.sec_id_col[0]), sqlCol("end_date"), sqlColAlias(<ret_1m>, "ret"), sqlColAlias(<cumulative_nav>, "nav"), sqlCol("ret_ytd_a"), sqlCol("ret_incep_a"))
  100. // TODO: how to make the "fund_id" dynamicly decided by tmp.sec_id_col[0]?
  101. s_where = expr(<fund_id>, in, s_entity_ids.strReplace("'", "").split(","))
  102. t = sql(s_col, tb_local, s_where).eval()
  103. }
  104. return t
  105. }
  106. /*
  107. * 取公私募基金月收益
  108. *
  109. * get_fund_monthly_ret("'MF00003PW1','MF00003PW1'", 1990.01.01, today(), true)
  110. */
  111. def get_fund_monthly_ret(fund_ids, start_date, end_date, isFromMySQL) {
  112. return get_monthly_ret('FD', fund_ids, start_date, end_date, isFromMySQL);
  113. }
  114. /*
  115. * 取无风险月度利率
  116. *
  117. * get_risk_free_rate(1990.01.01, today())
  118. */
  119. def get_risk_free_rate(start_date, end_date) {
  120. return get_monthly_ret('IX', "'IN0000000M'", start_date, end_date, true);
  121. }
  122. /*
  123. * 取基金最新收益及净值
  124. *
  125. * get_fund_latest_nav_performance("'HF000004KN','HF00018WXG'")
  126. */
  127. def get_fund_latest_nav_performance(fund_ids, isFromMySQL) {
  128. if(isFromMySQL == true) {
  129. s_query = "SELECT *
  130. FROM mfdb.fund_latest_nav_performance
  131. WHERE fund_id IN (" + fund_ids + ")
  132. AND isvalid = 1
  133. ORDER BY fund_id"
  134. conn = connect_mysql()
  135. t = odbc::query(conn, s_query)
  136. conn.close()
  137. } else {
  138. tb_local = load_table_from_local("fundit", "mfdb.fund_latest_nav_performance")
  139. s_col = sqlCol("*")
  140. s_where = expr(<fund_id>, in, fund_ids.strReplace("'", "").split(","))
  141. t = sql(s_col, tb_local, s_where).eval()
  142. }
  143. return t
  144. }
  145. /*
  146. * 取私募基金净值
  147. *
  148. *
  149. * Create: 202408 Joey
  150. * TODO: add isvalid and nav > 0 for local version
  151. *
  152. *
  153. * Example: get_nav_by_price_date('HF', "'HF000004KN','HF00018WXG'", 2024.05.01, true)
  154. */
  155. def get_nav_by_price_date(entity_type, entity_ids, price_date, isFromMySQL) {
  156. s_entity_ids = '';
  157. // 判断输入的 fund_ids 是字符串标量还是向量
  158. if ( entity_ids.form() == 0 ) {
  159. s_entity_ids = entity_ids;
  160. } else {
  161. s_entity_ids = "'" + entity_ids.concat("','") + "'";
  162. }
  163. tmp = get_nav_table_description(entity_type);
  164. if(isFromMySQL == true) {
  165. nav_table_name = "mfdb.nav"
  166. s_query = "SELECT " + tmp.sec_id_col[0] + ", price_date, " + tmp.cumulative_nav_col[0] + ", " + tmp.nav_col[0] + "
  167. FROM " + tmp.table_name[0] + "
  168. WHERE " + tmp.sec_id_col[0] + " IN (" + s_entity_ids + ")
  169. AND isvalid = 1
  170. AND " + tmp.cumulative_nav_col[0] + " > 0
  171. AND price_date >= '" + price_date$STRING + "'
  172. ORDER BY " + tmp.sec_id_col[0] + ", price_date";
  173. conn = connect_mysql();
  174. t = odbc::query(conn, s_query);
  175. conn.close();
  176. } else {
  177. tb_local = load_table_from_local("fundit", tmp.table_name[0])
  178. s_col = sqlCol("*")
  179. // TODO: how to make the "fund_id" dynamicly decided by tmp.sec_id_col[0]?
  180. s_where = [expr(<fund_id>, in, s_entity_ids.strReplace("'", "").split(",")), <price_date >= price_date>]
  181. t = sql(s_col, tb_local, s_where).eval()
  182. }
  183. return t
  184. }
  185. /*
  186. * 取指数因子点位
  187. *
  188. * get_index_nav_by_price_date("'IN00000008','FA00000WKG'", 2024.06.01)
  189. */
  190. def get_index_nav_by_price_date(index_ids, price_date) {
  191. s_query = "SELECT index_id, price_date, close AS cumulative_nav
  192. FROM mfdb.market_indexes
  193. WHERE index_id IN (" + index_ids + ")
  194. AND isvalid = 1
  195. AND close > 0
  196. AND price_date >= '" + price_date + "'
  197. UNION
  198. SELECT index_id AS index_id, price_date, index_value AS cumulative_nav
  199. FROM mfdb.indexes_ty_index
  200. WHERE index_id IN (" + index_ids + ")
  201. AND isvalid = 1
  202. AND index_value > 0
  203. AND price_date >= '" + price_date + "'
  204. UNION
  205. SELECT factor_id AS index_id, price_date, factor_value AS cumulative_nav
  206. FROM pfdb.cm_factor_value
  207. WHERE factor_id IN (" + index_ids + ")
  208. AND isvalid = 1
  209. AND factor_value > 0
  210. AND price_date >= '" + price_date + "'
  211. ORDER BY price_date"
  212. conn = connect_mysql()
  213. t = odbc::query(conn, s_query)
  214. conn.close()
  215. return t
  216. }
  217. /*
  218. * 取有效基金基本信息
  219. *
  220. * Example: get_fund_info("'HF000004KN','HF00018WXG'")
  221. *
  222. */
  223. def get_fund_info(fund_ids) {
  224. s_query = "SELECT fi.fund_id, fi.inception_date, fi.primary_benchmark_id AS benchmark_id, IFNULL(fi.initial_unit_value, 1) AS ini_value, fs.strategy, fs.substrategy
  225. FROM mfdb.fund_information fi
  226. INNER JOIN mfdb.fund_strategy fs ON fi.fund_id = fs.fund_id AND fs.isvalid = 1
  227. WHERE fi.fund_id IN (" + fund_ids + ")
  228. AND fi.isvalid = 1
  229. ORDER BY fi.fund_id"
  230. conn = connect_mysql()
  231. t = odbc::query(conn, s_query)
  232. conn.close()
  233. return t
  234. }
  235. /*
  236. * 取组合有效信息
  237. *
  238. * Example: get_portfolio_info('166002,166114');
  239. *
  240. */
  241. def get_portfolio_info(portfolio_ids) {
  242. s_query = "SELECT cpm.id AS portfolio_id, cpm.userid, cpm.customer_id, cpm.inception_date, cpm.portfolio_source, cpm.portfolio_type
  243. FROM pfdb.`pf_customer_portfolio_map` cpm
  244. INNER JOIN pfdb.cm_user u ON cpm.userid = u.userid
  245. WHERE cpm.id IN (" + portfolio_ids + ")
  246. AND cpm.isvalid = 1
  247. AND u.isvalid = 1
  248. ORDER BY cpm.id"
  249. conn = connect_mysql()
  250. t = odbc::query(conn, s_query)
  251. conn.close()
  252. return t
  253. }
  254. /*
  255. * 取私募基金净值更新信息, 返回基金及其净值更新的最早净值日期
  256. *
  257. * @param fund_ids: fund_id STRING VECTOR
  258. * @param update_time: all updates after this time
  259. *
  260. * Example: get_fund_list_by_nav_updatetime(null, 2024.07.19T10:00:00)
  261. *
  262. */
  263. def get_fund_list_by_nav_updatetime(fund_ids, updatetime) {
  264. s_fund_sql = '';
  265. // 这里要用 isVoid, 因为 isNull对向量返回的是布尔向量
  266. if (! isVoid(fund_ids)){
  267. s_fund_ids = fund_ids.concat("','");
  268. s_fund_sql = " AND fi.fund_id IN ('" + s_fund_ids + "')";
  269. }
  270. s_query = "SELECT fi.fund_id, MIN(nav.price_date) AS price_date,
  271. fi.inception_date, fi.primary_benchmark_id AS benchmark_id, IFNULL(fi.initial_unit_value, 1) AS ini_value
  272. FROM mfdb.fund_information fi
  273. INNER JOIN mfdb.nav ON fi.fund_id = nav.fund_id
  274. WHERE fi.isvalid = 1" +
  275. s_fund_sql + "
  276. AND nav.cumulative_nav > 0
  277. AND nav.updatetime >= '" + updatetime + "'
  278. GROUP BY fi.fund_id
  279. ORDER BY fi.fund_id"
  280. conn = connect_mysql()
  281. t = odbc::query(conn, s_query)
  282. conn.close()
  283. return t
  284. }
  285. /*
  286. * 取私募基金用于月末 fund_performance 表更新的净值
  287. *
  288. * @param fund_ids: 逗号分隔的ID字符串, 每个ID都有''
  289. * @param month_end: 月末日期字符串 YYYY-MM
  290. *
  291. *
  292. */
  293. def get_nav_for_hedge_fund_performance(fund_ids, month_end) {
  294. s_query = "CALL pfdb.sp_get_nav_for_fund_performance(" + fund_ids + ", '" + month_end + "', 1);"
  295. conn = connect_mysql()
  296. t = odbc::query(conn, s_query)
  297. conn.close()
  298. return t
  299. }
  300. /*
  301. * 取某月的基金BFI因子
  302. *
  303. * Example: get_fund_bfi_factors("'MF00003PW2', 'MF00003PW1', 'MF00003PXO'", '2024-06');
  304. */
  305. def get_fund_bfi_factors(fund_ids, month_end) {
  306. s_query = "SELECT fund_id, factor_id, end_date
  307. FROM pfdb.pf_fund_factor_bfi_by_category_group
  308. WHERE fund_id IN (" + fund_ids + ")
  309. AND end_date = '" + month_end + "'
  310. AND isvalid = 1;";
  311. conn = connect_mysql();
  312. t = odbc::query(conn, s_query);
  313. conn.close();
  314. return t;
  315. }
  316. /*
  317. * 取组合交易表
  318. *
  319. *
  320. * Example: get_portfolio_holding_history("166002,364640")
  321. */
  322. def get_portfolio_holding_history(portfolio_ids) {
  323. s_query = "SELECT portfolio_id, holding_date, fund_id, amount, fund_share, ROUND(amount/fund_share, 6) AS nav
  324. FROM pfdb.pf_portfolio_fund_history
  325. WHERE portfolio_id IN (" + portfolio_ids + ")
  326. AND isvalid = 1
  327. ORDER BY portfolio_id, holding_date";
  328. conn = connect_mysql();
  329. t = odbc::query(conn, s_query);
  330. conn.close();
  331. return t;
  332. }
  333. /*
  334. * 取基金证券从某日期后的所有净值
  335. * @param json_query <JSON>: [{sec_id:xxx, holding_date: yyyy-mm-dd}]
  336. *
  337. */
  338. def get_holding_nav(json_query) {
  339. s_query = "CALL pfdb.sp_get_nav_after_date('" + json_query + "')";
  340. conn = connect_mysql();
  341. t = odbc::query(conn, s_query);
  342. conn.close();
  343. return t;
  344. }