import fs from "node:fs/promises";
|
import { SpreadsheetFile, Workbook } from "@oai/artifact-tool";
|
|
const outputDir = "E:/mb-ms-doc/project-info/outputs/019f8e66-e563-7c01-a454-d66ee126f5d6";
|
const outputPath = `${outputDir}/2026Q2_机器人股票公募基金持仓统计_主动指数拆分.xlsx`;
|
|
const universe = [{"code":"000837","name":"秦川机床","csi":true,"cni":false},{"code":"000967","name":"盈峰环境","csi":true,"cni":true},{"code":"001266","name":"宏英智能","csi":true,"cni":false},{"code":"001306","name":"夏厦精密","csi":true,"cni":false},{"code":"002008","name":"大族激光","csi":true,"cni":false},{"code":"002031","name":"巨轮智能","csi":true,"cni":false},{"code":"002050","name":"三花智控","csi":true,"cni":true},{"code":"002139","name":"拓邦股份","csi":true,"cni":true},{"code":"002230","name":"科大讯飞","csi":true,"cni":true},{"code":"002236","name":"大华股份","csi":true,"cni":false},{"code":"002248","name":"华东数控","csi":true,"cni":false},{"code":"002338","name":"奥普光电","csi":false,"cni":true},{"code":"002380","name":"科远智慧","csi":true,"cni":false},{"code":"002472","name":"双环传动","csi":true,"cni":true},{"code":"002527","name":"新时达","csi":true,"cni":false},{"code":"002600","name":"领益智造","csi":false,"cni":true},{"code":"002747","name":"埃斯顿","csi":true,"cni":true},{"code":"002892","name":"科力尔","csi":true,"cni":true},{"code":"002896","name":"中大力德","csi":true,"cni":true},{"code":"002957","name":"科瑞技术","csi":true,"cni":false},{"code":"002975","name":"博杰股份","csi":true,"cni":false},{"code":"002979","name":"雷赛智能","csi":true,"cni":true},{"code":"003021","name":"兆威机电","csi":false,"cni":true},{"code":"300007","name":"汉威科技","csi":false,"cni":true},{"code":"300024","name":"机器人","csi":true,"cni":true},{"code":"300124","name":"汇川技术","csi":true,"cni":true},{"code":"300161","name":"华中数控","csi":true,"cni":true},{"code":"300222","name":"科大智能","csi":true,"cni":true},{"code":"300276","name":"三丰智能","csi":true,"cni":true},{"code":"300278","name":"华昌达","csi":true,"cni":false},{"code":"300432","name":"富临精工","csi":false,"cni":true},{"code":"300455","name":"航天智装","csi":false,"cni":true},{"code":"300466","name":"赛摩智能","csi":false,"cni":true},{"code":"300503","name":"昊志机电","csi":true,"cni":true},{"code":"300580","name":"贝斯特","csi":false,"cni":true},{"code":"300607","name":"拓斯达","csi":true,"cni":true},{"code":"300660","name":"江苏雷利","csi":true,"cni":false},{"code":"300802","name":"矩子科技","csi":true,"cni":false},{"code":"300953","name":"震裕科技","csi":false,"cni":true},{"code":"301112","name":"信邦智能","csi":true,"cni":false},{"code":"301368","name":"丰立智能","csi":true,"cni":true},{"code":"301413","name":"安培龙","csi":false,"cni":true},{"code":"301510","name":"固高科技","csi":true,"cni":true},{"code":"601100","name":"恒立液压","csi":true,"cni":false},{"code":"601608","name":"中信重工","csi":true,"cni":true},{"code":"601689","name":"拓普集团","csi":true,"cni":true},{"code":"603015","name":"弘讯科技","csi":true,"cni":true},{"code":"603119","name":"浙江荣泰","csi":false,"cni":true},{"code":"603203","name":"快克智能","csi":true,"cni":false},{"code":"603416","name":"信捷电气","csi":true,"cni":true},{"code":"603486","name":"科沃斯","csi":true,"cni":true},{"code":"603662","name":"柯力传感","csi":false,"cni":true},{"code":"603666","name":"亿嘉和","csi":true,"cni":false},{"code":"603728","name":"鸣志电器","csi":true,"cni":true},{"code":"603915","name":"国茂股份","csi":true,"cni":true},{"code":"603960","name":"克来机电","csi":true,"cni":true},{"code":"688003","name":"天准科技","csi":true,"cni":false},{"code":"688017","name":"绿的谐波","csi":true,"cni":true},{"code":"688084","name":"晶品特装","csi":true,"cni":false},{"code":"688090","name":"瑞松科技","csi":true,"cni":false},{"code":"688160","name":"步科股份","csi":true,"cni":true},{"code":"688165","name":"埃夫特","csi":true,"cni":true},{"code":"688169","name":"石头科技","csi":true,"cni":true},{"code":"688188","name":"柏楚电子","csi":true,"cni":false},{"code":"688218","name":"江苏北人","csi":true,"cni":false},{"code":"688248","name":"南网科技","csi":true,"cni":true},{"code":"688277","name":"天智航","csi":true,"cni":true},{"code":"688279","name":"峰岹科技","csi":false,"cni":true},{"code":"688290","name":"景业智能","csi":true,"cni":false},{"code":"688306","name":"均普智能","csi":true,"cni":false},{"code":"688320","name":"禾川科技","csi":true,"cni":false},{"code":"688322","name":"奥比中光","csi":true,"cni":true},{"code":"688343","name":"云天励飞","csi":true,"cni":false},{"code":"688400","name":"凌云光","csi":false,"cni":true},{"code":"688686","name":"奥普特","csi":true,"cni":true},{"code":"688698","name":"伟创电气","csi":true,"cni":true},{"code":"688777","name":"中控技术","csi":true,"cni":false},{"code":"920593","name":"鼎智科技","csi":true,"cni":true}];
|
|
const dataUrl = (page) => `https://data.eastmoney.com/dataapi/zlsj/list?date=2026-06-30&type=1&zjc=0&sortField=HOLD_VALUE&sortDirec=1&pageNum=${page}&pageSize=500`;
|
async function getJson(url) {
|
let lastError;
|
for (let attempt = 1; attempt <= 3; attempt++) {
|
try {
|
const response = await fetch(url, { headers: { "User-Agent": "Mozilla/5.0" } });
|
if (!response.ok) throw new Error(`HTTP ${response.status}: ${url}`);
|
return response.json();
|
} catch (error) {
|
lastError = error;
|
if (attempt < 3) await new Promise((resolve) => setTimeout(resolve, 350 * attempt));
|
}
|
}
|
throw lastError;
|
}
|
|
const first = await getJson(dataUrl(1));
|
const pageCount = Number(first.pages || 1);
|
const rest = pageCount > 1 ? await Promise.all(Array.from({ length: pageCount - 1 }, (_, i) => getJson(dataUrl(i + 2)))) : [];
|
const sourceRows = [first, ...rest].flatMap((x) => x.data || []);
|
const sourceMap = new Map(sourceRows.map((x) => [String(x.SECURITY_CODE), x]));
|
|
const fundCatalogResponse = await fetch("https://fund.eastmoney.com/js/fundcode_search.js", { headers: { "User-Agent": "Mozilla/5.0" } });
|
if (!fundCatalogResponse.ok) throw new Error(`HTTP ${fundCatalogResponse.status}: fund catalog`);
|
const fundCatalogText = await fundCatalogResponse.text();
|
const fundCatalogRows = JSON.parse(fundCatalogText.slice(fundCatalogText.indexOf("["), fundCatalogText.lastIndexOf("]") + 1));
|
const fundCatalog = new Map(fundCatalogRows.map((row) => [String(row[0]), { name: String(row[2] || ""), type: String(row[3] || "") }]));
|
|
const fundDetailUrl = (code, page) => `https://data.eastmoney.com/dataapi/zlsj/detail?SHType=1&SHCode=&SCode=${code}&ReportDate=2026-06-30&sortField=HOLDER_CODE&sortDirec=1&pageNum=${page}&pageSize=200`;
|
async function loadFundDetails(code) {
|
const firstPage = await getJson(fundDetailUrl(code, 1));
|
const pages = Number(firstPage.pages || 1);
|
const morePages = pages > 1 ? await Promise.all(Array.from({ length: pages - 1 }, (_, i) => getJson(fundDetailUrl(code, i + 2)))) : [];
|
return [firstPage, ...morePages].flatMap((x) => x.data || []);
|
}
|
async function pooledMap(items, limit, mapper) {
|
const results = new Array(items.length);
|
let cursor = 0;
|
async function worker() {
|
while (true) {
|
const index = cursor++;
|
if (index >= items.length) return;
|
results[index] = await mapper(items[index], index);
|
}
|
}
|
await Promise.all(Array.from({ length: Math.min(limit, items.length) }, () => worker()));
|
return results;
|
}
|
|
const rawRelationGroups = await pooledMap(universe, 6, async (stock) => {
|
const rows = await loadFundDetails(stock.code);
|
return rows.map((row) => ({ ...row, INDEX_LABEL: stock.csi && stock.cni ? "双指数" : stock.csi ? "中证H30590" : "国证980022" }));
|
});
|
for (let i = 0; i < universe.length; i++) {
|
const stock = universe[i];
|
const expectedCount = Number(sourceMap.get(stock.code)?.HOULD_NUM || 0);
|
if (rawRelationGroups[i].length === expectedCount) continue;
|
for (let retry = 1; retry <= 4; retry++) {
|
await new Promise((resolve) => setTimeout(resolve, 700 * retry));
|
const rows = await loadFundDetails(stock.code);
|
rawRelationGroups[i] = rows.map((row) => ({ ...row, INDEX_LABEL: stock.csi && stock.cni ? "双指数" : stock.csi ? "中证H30590" : "国证980022" }));
|
if (rawRelationGroups[i].length === expectedCount) break;
|
}
|
if (rawRelationGroups[i].length !== expectedCount) {
|
throw new Error(`基金明细数量未对账：${stock.code} expected=${expectedCount} actual=${rawRelationGroups[i].length}`);
|
}
|
}
|
const rawRelations = rawRelationGroups.flat();
|
|
const indexNamePattern = /ETF|指数|联接/iu;
|
const fundRelations = rawRelations.map((row) => {
|
const fundCode = String(row.HOLDER_CODE || "");
|
const fundName = String(row.HOLDER_NAME || "");
|
const catalog = fundCatalog.get(fundCode);
|
const fundType = catalog?.type || "未匹配";
|
const managementType = fundType.startsWith("指数型") || indexNamePattern.test(fundName) ? "指数型" : "主动型";
|
return {
|
stockCode: String(row.SECURITY_CODE || ""),
|
stockName: String(row.SECURITY_NAME_ABBR || ""),
|
index: String(row.INDEX_LABEL || ""),
|
fundCode,
|
fundName,
|
fundType,
|
managementType,
|
fundCompany: String(row.PARENT_ORG_NAME || ""),
|
holdSharesWan: Number(row.TOTAL_SHARES || 0) / 1e4,
|
holdValueYi: Number(row.HOLD_MARKET_CAP || 0) / 1e8,
|
totalCapRatio: Number(row.TOTAL_SHARES_RATIO || 0) / 100,
|
floatRatio: Number(row.FREE_SHARES_RATIO || 0) / 100,
|
navRatio: Number(row.NETASSET_RATIO || 0) / 100,
|
};
|
});
|
const relationsByStock = new Map(universe.map((stock) => [stock.code, []]));
|
for (const relation of fundRelations) relationsByStock.get(relation.stockCode)?.push(relation);
|
|
const indexLabel = (x) => x.csi && x.cni ? "双指数" : x.csi ? "中证H30590" : "国证980022";
|
const listedCode = (code) => `${code}.${code.startsWith("6") ? "SH" : code.startsWith("9") ? "BJ" : "SZ"}`;
|
const data = universe.map((stock) => {
|
const d = sourceMap.get(stock.code);
|
const relations = relationsByStock.get(stock.code) || [];
|
const activeRelations = relations.filter((x) => x.managementType === "主动型");
|
const indexRelations = relations.filter((x) => x.managementType === "指数型");
|
return {
|
...stock,
|
index: indexLabel(stock),
|
fundCount: d ? Number(d.HOULD_NUM || 0) : 0,
|
activeFundCount: activeRelations.length,
|
indexFundCount: indexRelations.length,
|
holdSharesWan: d ? Number(d.TOTAL_SHARES || 0) / 1e4 : 0,
|
holdValueYi: d ? Number(d.HOLD_VALUE || 0) / 1e8 : 0,
|
activeHoldValueYi: activeRelations.reduce((sum, x) => sum + x.holdValueYi, 0),
|
indexHoldValueYi: indexRelations.reduce((sum, x) => sum + x.holdValueYi, 0),
|
totalCapRatio: d ? Number(d.TOTALSHARES_RATIO || 0) / 100 : 0,
|
floatRatio: d ? Number(d.FREESHARES_RATIO || 0) / 100 : 0,
|
q2ShareChangeWan: d ? Number(d.HOLDCHA_NUM || 0) / 1e4 : 0,
|
direction: d ? String(d.HOLDCHA || "-") : "未进入重仓统计",
|
status: d ? "已进入基金重仓统计" : "未进入基金重仓统计",
|
};
|
}).sort((a, b) => b.holdValueYi - a.holdValueYi || b.fundCount - a.fundCount || a.code.localeCompare(b.code));
|
|
const uniqueActiveFunds = new Set(fundRelations.filter((x) => x.managementType === "主动型").map((x) => x.fundCode));
|
const uniqueIndexFunds = new Set(fundRelations.filter((x) => x.managementType === "指数型").map((x) => x.fundCode));
|
|
const workbook = Workbook.create();
|
const summary = workbook.worksheets.add("摘要");
|
const detail = workbook.worksheets.add("明细");
|
const fundDetail = workbook.worksheets.add("基金级明细");
|
const sources = workbook.worksheets.add("来源与口径");
|
const checks = workbook.worksheets.add("检查");
|
for (const sheet of [summary, detail, fundDetail, sources, checks]) sheet.showGridLines = false;
|
|
const navy = "#17365D";
|
const teal = "#0F6B78";
|
const lightBlue = "#DCE6F1";
|
const pale = "#F4F7FA";
|
const green = "#E2F0D9";
|
const red = "#FCE4D6";
|
const yellow = "#FFF2CC";
|
const gray = "#666666";
|
|
// 明细表
|
detail.mergeCells("A1:V1");
|
detail.getRange("A1").values = [["2026年二季度机器人相关股票公募基金持仓统计"]];
|
detail.getRange("A1:V1").format = { fill: navy, font: { bold: true, color: "#FFFFFF", size: 16 }, verticalAlignment: "center" };
|
detail.getRange("A1:V1").format.rowHeight = 30;
|
detail.mergeCells("A2:V2");
|
detail.getRange("A2").values = [["股票范围：中证机器人H30590与国证机器人产业980022成分股并集（78只）；总持仓已包含主动型和指数型基金，本版新增主动/指数拆分。"]];
|
detail.getRange("A2:V2").format = { fill: pale, font: { color: gray, size: 10 }, wrapText: true, verticalAlignment: "center" };
|
detail.getRange("A2:V2").format.rowHeight = 30;
|
const headers = ["排名","股票代码","股票简称","指数归属","持有基金数","期末持股数(万股)","期末持仓市值(亿元)","占总股本","占流通股本","Q2增减持股数(万股)","6/30收盘价(元)","Q2净增减持估算(亿元)","增减方向","披露状态","来源ID","主动型基金数","指数型基金数","主动型持仓市值(亿元)","指数型持仓市值(亿元)","主动型占基金持仓","主动型持仓占总股本","指数型持仓占总股本"];
|
detail.getRange("A4:V4").values = [headers];
|
detail.getRange("A4:V4").format = { fill: teal, font: { bold: true, color: "#FFFFFF" }, wrapText: true, horizontalAlignment: "center", verticalAlignment: "center" };
|
detail.getRange("A4:V4").format.rowHeight = 38;
|
const detailValues = data.map((d, i) => [i + 1, null, d.name, d.index, d.fundCount, d.holdSharesWan, d.holdValueYi, d.totalCapRatio, d.floatRatio, d.q2ShareChangeWan, null, null, d.direction, d.status, "EM-Q2-2026", d.activeFundCount, d.indexFundCount, d.activeHoldValueYi, d.indexHoldValueYi, null, null, null]);
|
detail.getRange(`A5:V${data.length + 4}`).values = detailValues;
|
for (let row = 5; row <= data.length + 4; row++) {
|
detail.getRange(`B${row}`).formulas = [[`="${listedCode(data[row - 5].code)}"`]];
|
detail.getRange(`K${row}`).formulas = [[`=IFERROR(G${row}/F${row}*10000,0)`]];
|
detail.getRange(`L${row}`).formulas = [[`=IFERROR(J${row}*K${row}/10000,0)`]];
|
detail.getRange(`T${row}`).formulas = [[`=IFERROR(R${row}/G${row},0)`]];
|
detail.getRange(`U${row}`).formulas = [[`=IFERROR(R${row}/G${row}*H${row},0)`]];
|
detail.getRange(`V${row}`).formulas = [[`=IFERROR(S${row}/G${row}*H${row},0)`]];
|
}
|
detail.getRange(`A5:V${data.length + 4}`).format.font = { name: "Microsoft YaHei", size: 10 };
|
detail.getRange(`A5:D${data.length + 4}`).format.horizontalAlignment = "left";
|
detail.getRange(`E5:L${data.length + 4}`).format.horizontalAlignment = "right";
|
detail.getRange(`A5:A${data.length + 4}`).format.numberFormat = "#,##0";
|
detail.getRange(`B5:B${data.length + 4}`).format.numberFormat = "@";
|
detail.getRange(`E5:E${data.length + 4}`).format.numberFormat = "#,##0;[Red](#,##0);-";
|
detail.getRange(`F5:G${data.length + 4}`).format.numberFormat = "#,##0.00;[Red](#,##0.00);-";
|
detail.getRange(`H5:I${data.length + 4}`).format.numberFormat = "0.00%;[Red](0.00%);-";
|
detail.getRange(`J5:J${data.length + 4}`).format.numberFormat = "#,##0.00;[Red](#,##0.00);-";
|
detail.getRange(`K5:K${data.length + 4}`).format.numberFormat = "0.00";
|
detail.getRange(`L5:L${data.length + 4}`).format.numberFormat = "#,##0.00;[Red](#,##0.00);-";
|
detail.getRange(`P5:Q${data.length + 4}`).format.numberFormat = "#,##0;[Red](#,##0);-";
|
detail.getRange(`R5:S${data.length + 4}`).format.numberFormat = "#,##0.00;[Red](#,##0.00);-";
|
detail.getRange(`T5:V${data.length + 4}`).format.numberFormat = "0.00%;[Red](0.00%);-";
|
detail.getRange(`E5:E${data.length + 4}`).conditionalFormats.add("dataBar", { color: "#5B9BD5", gradient: true });
|
detail.getRange(`G5:G${data.length + 4}`).conditionalFormats.add("dataBar", { color: "#70AD47", gradient: true });
|
detail.getRange(`P5:Q${data.length + 4}`).conditionalFormats.add("dataBar", { color: "#5B9BD5", gradient: true });
|
detail.getRange(`R5:S${data.length + 4}`).conditionalFormats.add("dataBar", { color: "#70AD47", gradient: true });
|
detail.getRange(`H5:I${data.length + 4}`).conditionalFormats.add("colorScale", { colors: ["#FFFFFF", "#FFE699", "#F8696B"], thresholds: ["min", "50%", "max"] });
|
detail.getRange(`L5:L${data.length + 4}`).conditionalFormats.add("cellIs", { operator: "greaterThan", formula: 0, format: { fill: green, font: { color: "#006100" } } });
|
detail.getRange(`L5:L${data.length + 4}`).conditionalFormats.add("cellIs", { operator: "lessThan", formula: 0, format: { fill: red, font: { color: "#9C0006" } } });
|
detail.getRange(`N5:N${data.length + 4}`).conditionalFormats.add("containsText", { text: "未进入", format: { fill: yellow, font: { color: "#9C6500" } } });
|
const detailTable = detail.tables.add(`A4:V${data.length + 4}`, true, "RobotFundHoldingsTable");
|
detailTable.style = "TableStyleMedium2";
|
detail.freezePanes.freezeRows(4);
|
detail.freezePanes.freezeColumns(3);
|
const widths = { A: 7, B: 11, C: 12, D: 13, E: 11, F: 16, G: 18, H: 12, I: 13, J: 18, K: 14, L: 20, M: 11, N: 20, O: 14, P: 13, Q: 13, R: 19, S: 19, T: 17, U: 18, V: 18 };
|
for (const [col, width] of Object.entries(widths)) detail.getRange(`${col}:${col}`).format.columnWidth = width;
|
|
// 基金级明细
|
fundDetail.mergeCells("A1:N1");
|
fundDetail.getRange("A1").values = [["2026Q2机器人股票基金级持仓明细｜主动型与指数型拆分"]];
|
fundDetail.getRange("A1:N1").format = { fill: navy, font: { bold: true, color: "#FFFFFF", size: 16 }, verticalAlignment: "center" };
|
fundDetail.getRange("A1:N1").format.rowHeight = 30;
|
fundDetail.mergeCells("A2:N2");
|
fundDetail.getRange("A2").values = [["主动型定义：持仓基金明细中的非指数型产品（含公募化资管产品）；指数型包含ETF、ETF联接、普通指数及增强指数。每行是一条“股票-基金”持仓关系。"]];
|
fundDetail.getRange("A2:N2").format = { fill: yellow, font: { color: "#7F6000", size: 10 }, wrapText: true, verticalAlignment: "center" };
|
fundDetail.getRange("A2:N2").format.rowHeight = 32;
|
const fundDetailHeaders = ["股票代码","股票简称","指数归属","基金代码","基金名称","基金公司","基金分类","管理类型","持股数(万股)","持仓市值(亿元)","占股票总股本","占股票流通股本","占基金净值","来源URL"];
|
fundDetail.getRange("A4:N4").values = [fundDetailHeaders];
|
fundDetail.getRange("A4:N4").format = { fill: teal, font: { bold: true, color: "#FFFFFF" }, wrapText: true, horizontalAlignment: "center", verticalAlignment: "center" };
|
fundDetail.getRange("A4:N4").format.rowHeight = 34;
|
const fundDetailSorted = [...fundRelations].sort((a, b) => b.holdValueYi - a.holdValueYi || a.stockCode.localeCompare(b.stockCode) || a.fundCode.localeCompare(b.fundCode));
|
const fundDetailValues = fundDetailSorted.map((x) => [listedCode(x.stockCode), x.stockName, x.index, x.fundCode, x.fundName, x.fundCompany, x.fundType, x.managementType, x.holdSharesWan, x.holdValueYi, x.totalCapRatio, x.floatRatio, x.navRatio, fundDetailUrl(x.stockCode, 1)]);
|
const fundDetailLastRow = fundDetailValues.length + 4;
|
fundDetail.getRange(`A5:N${fundDetailLastRow}`).values = fundDetailValues;
|
fundDetail.getRange(`A5:N${fundDetailLastRow}`).format.font = { name: "Microsoft YaHei", size: 9 };
|
fundDetail.getRange(`A5:H${fundDetailLastRow}`).format.horizontalAlignment = "left";
|
fundDetail.getRange(`I5:M${fundDetailLastRow}`).format.horizontalAlignment = "right";
|
fundDetail.getRange(`A5:A${fundDetailLastRow}`).format.numberFormat = "@";
|
fundDetail.getRange(`D5:D${fundDetailLastRow}`).format.numberFormat = "@";
|
fundDetail.getRange(`I5:J${fundDetailLastRow}`).format.numberFormat = "#,##0.00;[Red](#,##0.00);-";
|
fundDetail.getRange(`K5:M${fundDetailLastRow}`).format.numberFormat = "0.00%;[Red](0.00%);-";
|
fundDetail.getRange(`H5:H${fundDetailLastRow}`).conditionalFormats.add("containsText", { text: "主动型", format: { fill: green, font: { color: "#006100" } } });
|
fundDetail.getRange(`H5:H${fundDetailLastRow}`).conditionalFormats.add("containsText", { text: "指数型", format: { fill: lightBlue, font: { color: navy } } });
|
fundDetail.getRange(`J5:J${fundDetailLastRow}`).conditionalFormats.add("dataBar", { color: "#70AD47", gradient: true });
|
fundDetail.getRange(`N5:N${fundDetailLastRow}`).format.font = { color: "#FF0000", size: 8 };
|
const fundRelationsTable = fundDetail.tables.add(`A4:N${fundDetailLastRow}`, true, "RobotFundRelationsTable");
|
fundRelationsTable.style = "TableStyleMedium2";
|
fundDetail.freezePanes.freezeRows(4);
|
fundDetail.freezePanes.freezeColumns(5);
|
const fundDetailWidths = { A: 12, B: 12, C: 13, D: 11, E: 32, F: 28, G: 18, H: 12, I: 14, J: 18, K: 16, L: 16, M: 14, N: 72 };
|
for (const [col, width] of Object.entries(fundDetailWidths)) fundDetail.getRange(`${col}:${col}`).format.columnWidth = width;
|
|
// 摘要页
|
summary.mergeCells("A1:W2");
|
summary.getRange("A1").values = [["机器人股票的公募基金持仓全景｜2026Q2主动/指数拆分"]];
|
summary.getRange("A1:W2").format = { fill: navy, font: { bold: true, color: "#FFFFFF", size: 18 }, verticalAlignment: "center" };
|
const cards = [
|
{ label: "机器人股票并集", labelRange: "A4:D4", valueRange: "A5:D6", formula: "=COUNTA('明细'!$B$5:$B$82)", format: "#,##0\"只\"" },
|
{ label: "主动型基金-股票关系", labelRange: "F4:I4", valueRange: "F5:I6", formula: "=SUM('明细'!$P$5:$P$82)", format: "#,##0\"条\"" },
|
{ label: "指数型基金-股票关系", labelRange: "K4:N4", valueRange: "K5:N6", formula: "=SUM('明细'!$Q$5:$Q$82)", format: "#,##0\"条\"" },
|
{ label: "主动型重仓持仓市值", labelRange: "P4:S4", valueRange: "P5:S6", formula: "=SUM('明细'!$R$5:$R$82)", format: "#,##0.00\"亿元\"" },
|
{ label: "指数型重仓持仓市值", labelRange: "U4:W4", valueRange: "U5:W6", formula: "=SUM('明细'!$S$5:$S$82)", format: "#,##0.00\"亿元\"" },
|
];
|
for (const card of cards) {
|
summary.mergeCells(card.labelRange);
|
summary.mergeCells(card.valueRange);
|
summary.getRange(card.labelRange.split(":")[0]).values = [[card.label]];
|
summary.getRange(card.valueRange.split(":")[0]).formulas = [[card.formula]];
|
summary.getRange(card.labelRange).format = { fill: lightBlue, font: { bold: true, color: navy }, horizontalAlignment: "center", verticalAlignment: "center", borders: { preset: "outside", style: "thin", color: "#A6A6A6" } };
|
summary.getRange(card.valueRange).format = { fill: "#FFFFFF", font: { bold: true, color: navy, size: 16 }, horizontalAlignment: "center", verticalAlignment: "center", numberFormat: card.format, borders: { preset: "outside", style: "thin", color: "#A6A6A6" } };
|
}
|
summary.mergeCells("A8:W9");
|
summary.getRange("A8").values = [["重要口径：原总数已包含主动型与指数型基金；本版按基金分类拆分。主动型=非指数型产品（含公募化资管），指数型含ETF、联接、普通指数及增强指数。当前仍是二季报前十大重仓股口径，并非8月底半年报完整持仓。"]];
|
summary.getRange("A8:W9").format = { fill: yellow, font: { color: "#7F6000", size: 10 }, wrapText: true, verticalAlignment: "center", borders: { preset: "outside", style: "thin", color: "#D6B656" } };
|
|
summary.getRange("A11:J11").values = [["排名","股票代码","股票简称","指数归属","总基金数","主动型基金数","指数型基金数","总持仓市值(亿元)","主动型持仓市值(亿元)","主动型占基金持仓"]];
|
summary.getRange("A11:J11").format = { fill: teal, font: { bold: true, color: "#FFFFFF" }, wrapText: true, horizontalAlignment: "center", verticalAlignment: "center" };
|
for (let i = 0; i < 50; i++) {
|
const r = 12 + i;
|
const d = 5 + i;
|
summary.getRange(`A${r}:J${r}`).formulas = [[`='明细'!A${d}`, `='明细'!B${d}`, `='明细'!C${d}`, `='明细'!D${d}`, `='明细'!E${d}`, `='明细'!P${d}`, `='明细'!Q${d}`, `='明细'!G${d}`, `='明细'!R${d}`, `='明细'!T${d}`]];
|
}
|
summary.getRange("A12:A61").format.numberFormat = "#,##0";
|
summary.getRange("B12:B61").format.numberFormat = "@";
|
summary.getRange("E12:G61").format.numberFormat = "#,##0";
|
summary.getRange("H12:I61").format.numberFormat = "#,##0.00;[Red](#,##0.00);-";
|
summary.getRange("J12:J61").format.numberFormat = "0.00%";
|
summary.getRange("A11:J61").format.borders = { preset: "inside", style: "thin", color: "#D9E2F3" };
|
summary.getRange("I12:I61").conditionalFormats.add("dataBar", { color: "#70AD47", gradient: true });
|
summary.getRange("J12:J61").conditionalFormats.add("colorScale", { colors: ["#FFFFFF", "#FFE699", "#70AD47"], thresholds: ["min", "50%", "max"] });
|
|
summary.getRange("A66:B66").values = [["股票", "主动型持仓市值(亿元)"]];
|
summary.getRange("D66:E66").values = [["股票", "主动型基金数"]];
|
const detailRowByCode = new Map(data.map((item, index) => [item.code, index + 5]));
|
const activeValueTop = [...data].sort((a, b) => b.activeHoldValueYi - a.activeHoldValueYi || b.activeFundCount - a.activeFundCount || a.code.localeCompare(b.code));
|
const fundCountTop = [...data].sort((a, b) => b.activeFundCount - a.activeFundCount || b.activeHoldValueYi - a.activeHoldValueYi || a.code.localeCompare(b.code));
|
for (let i = 0; i < 15; i++) {
|
const s = 67 + i;
|
const activeValueRow = detailRowByCode.get(activeValueTop[i].code);
|
const fundRow = detailRowByCode.get(fundCountTop[i].code);
|
summary.getRange(`A${s}:B${s}`).formulas = [[`='明细'!C${activeValueRow}`, `='明细'!R${activeValueRow}`]];
|
summary.getRange(`D${s}:E${s}`).formulas = [[`='明细'!C${fundRow}`, `='明细'!P${fundRow}`]];
|
}
|
const valueChart = summary.charts.add("bar", summary.getRange("A66:B81"));
|
valueChart.title = "主动型重仓持仓市值TOP15（亿元）";
|
valueChart.hasLegend = false;
|
valueChart.xAxis = { axisType: "textAxis", textStyle: { fontSize: 9 } };
|
valueChart.yAxis = { numberFormatCode: "#,##0", textStyle: { fontSize: 9 } };
|
valueChart.setPosition("L11", "Q27");
|
const countChart = summary.charts.add("bar", summary.getRange("D66:E81"));
|
countChart.title = "主动型基金数TOP15（只）";
|
countChart.hasLegend = false;
|
countChart.xAxis = { axisType: "textAxis", textStyle: { fontSize: 9 } };
|
countChart.yAxis = { numberFormatCode: "#,##0", textStyle: { fontSize: 9 } };
|
countChart.setPosition("R11", "W27");
|
|
summary.getRange("A63:W63").merge();
|
summary.getRange("A63").values = [["摘要展示前50；完整78只股票拆分见“明细”，逐基金持仓见“基金级明细”，数据解释与限制见“来源与口径”。"]];
|
summary.getRange("A63:W63").format = { fill: pale, font: { italic: true, color: gray }, wrapText: true };
|
summary.freezePanes.freezeRows(2);
|
const summaryWidths = { A: 7, B: 11, C: 12, D: 13, E: 10, F: 12, G: 12, H: 16, I: 18, J: 16, K: 2, L: 10, M: 10, N: 10, O: 10, P: 10, Q: 10, R: 10, S: 10, T: 10, U: 10, V: 10, W: 10 };
|
for (const [col, width] of Object.entries(summaryWidths)) summary.getRange(`${col}:${col}`).format.columnWidth = width;
|
|
// 来源与口径
|
sources.mergeCells("A1:F1");
|
sources.getRange("A1").values = [["来源、定义与限制"]];
|
sources.getRange("A1:F1").format = { fill: navy, font: { bold: true, color: "#FFFFFF", size: 16 } };
|
sources.getRange("A3:F3").values = [["来源ID","用途","日期/期间","数据提供方","URL","说明"]];
|
sources.getRange("A3:F3").format = { fill: teal, font: { bold: true, color: "#FFFFFF" }, wrapText: true };
|
const sourceData = [
|
["CSI-H30590","中证机器人指数成分股与权重","2026-06-30","中证指数有限公司","https://oss-ch.csindex.com.cn/static/html/csindex/public/uploads/file/autofile/closeweight/H30590closeweight.xls","官方样本权重表；64只"],
|
["CNI-980022","国证机器人产业指数成分股","2026-07-23（6月调样后）","深圳证券信息有限公司","https://www.cnindex.com.cn/sample-detail/detail?indexcode=980022&dateStr=2026-07&pageNum=1&rows=100&isFirstCall=1","官方当前成分表；50只"],
|
["EM-Q2-2026","公募基金重仓持股汇总","2026-06-30","东方财富Choice数据","https://data.eastmoney.com/zlsj/bx.html","字段包括持有基金数、持股数、持仓市值、占总股本/流通股比例、环比持股变化"],
|
["EM-API","逐股汇总接口","2026-06-30","东方财富数据中心","https://data.eastmoney.com/dataapi/zlsj/list?date=2026-06-30&type=1&zjc=0&sortField=HOLD_VALUE&sortDirec=1&pageNum=1&pageSize=500","本工作簿抓取全部分页后与78只指数成分股并集匹配"],
|
["EM-FUND-DETAIL","逐股票的持仓基金明细","2026-06-30","东方财富数据中心","https://data.eastmoney.com/dataapi/zlsj/detail?SHType=1&SCode={股票代码}&ReportDate=2026-06-30&pageNum=1&pageSize=200","用于拆分每只股票的主动型/指数型基金数量及持仓市值；逐行来源链接见基金级明细"],
|
["EM-FUND-CATALOG","基金名称与基金分类","抓取日2026-07-24","天天基金网","https://fund.eastmoney.com/js/fundcode_search.js","指数型按基金分类及名称识别；增强指数归入指数型，其余非指数产品归入主动型"],
|
];
|
sources.getRange("A4:F9").values = sourceData;
|
sources.getRange("A4:F9").format.wrapText = true;
|
sources.getRange("4:9").format.rowHeight = 46;
|
sources.getRange("E4:E9").format.font = { color: "#FF0000" };
|
sources.getRange("A11:F11").merge();
|
sources.getRange("A11").values = [["统计定义"]];
|
sources.getRange("A11:F11").format = { fill: lightBlue, font: { bold: true, color: navy } };
|
const definitions = [
|
["机器人股票范围","H30590与980022成分股去重后的并集，共78只。","同一股票若同时属于两个指数，指数归属标为“双指数”。"],
|
["持有基金数","东方财富Choice在该报告期统计的基金数量。","是逐股票的基金数；不同股票之间不可简单去重，汇总后称“股票-基金持仓关系”。"],
|
["主动型基金","持仓基金明细中的非指数型产品，包括股票型、混合型、灵活配置及公募化资管产品。","采用“非指数型”操作口径；未进一步按权益仓位、投资风格或是否量化细分。"],
|
["指数型基金","基金分类为指数型，或名称含ETF、指数、联接的产品。","包含普通指数、ETF、ETF联接及增强指数；增强指数未归入主动型。"],
|
["期末持仓市值","进入公开重仓统计的基金持股数量×2026-06-30股价。","是公开重仓口径下限，不代表全市场公募实际完整持仓。"],
|
["占股票总市值","基金披露持股数÷公司总股本，等价于持仓市值÷总市值。","表中直接采用数据源TOTALSHARES_RATIO字段。"],
|
["Q2净增减持估算","二季度披露持股数量变化×2026-06-30收盘价。","不是实际买卖成交额，忽略成交时点、成交价格及季度内往返交易。"],
|
["0只基金的解释","未进入当前基金重仓统计。","不等于所有基金都完全没有持有；基金二季报通常只披露前十大重仓股。"],
|
];
|
sources.getRange("A12:C20").values = [["项目","定义","限制/解释"], ...definitions];
|
sources.getRange("A12:C12").format = { fill: teal, font: { bold: true, color: "#FFFFFF" } };
|
sources.getRange("A12:C20").format.wrapText = true;
|
sources.getRange("13:20").format.rowHeight = 58;
|
sources.getRange("A21:F21").merge();
|
sources.getRange("A21").values = [["更新提示：截至2026-07-24，当前统计仍以基金二季报前十大重仓股为主。完整半年报通常要到8月底披露，届时可用全部持仓重新统计，结果通常会高于当前口径。"]];
|
sources.getRange("A21:F21").format = { fill: yellow, font: { color: "#7F6000" }, wrapText: true };
|
sources.getRange("21:21").format.rowHeight = 34;
|
const sourceWidths = { A: 20, B: 42, C: 44, D: 24, E: 75, F: 48 };
|
for (const [col, width] of Object.entries(sourceWidths)) sources.getRange(`${col}:${col}`).format.columnWidth = width;
|
sources.getRange("A1:F21").format.font = { name: "Microsoft YaHei", size: 10 };
|
sources.freezePanes.freezeRows(3);
|
|
// 检查页
|
checks.mergeCells("A1:G1");
|
checks.getRange("A1").values = [["数据与公式检查"]];
|
checks.getRange("A1:G1").format = { fill: navy, font: { bold: true, color: "#FFFFFF", size: 16 } };
|
checks.getRange("A3:G3").values = [["检查项","实际值","预期值","差异","容差","状态","说明"]];
|
checks.getRange("A3:G3").format = { fill: teal, font: { bold: true, color: "#FFFFFF" } };
|
const checkRows = [
|
["股票并集行数", "=COUNTA('明细'!$B$5:$B$82)", 78, "=B4-C4", 0, "=IF(ABS(D4)<=E4,\"OK\",\"FAIL\")", "H30590与980022去重并集"],
|
["中证成分股数量", "=COUNTIF('明细'!$D$5:$D$82,\"中证H30590\")+COUNTIF('明细'!$D$5:$D$82,\"双指数\")", 64, "=B5-C5", 0, "=IF(ABS(D5)<=E5,\"OK\",\"FAIL\")", "官方H30590样本数"],
|
["国证成分股数量", "=COUNTIF('明细'!$D$5:$D$82,\"国证980022\")+COUNTIF('明细'!$D$5:$D$82,\"双指数\")", 50, "=B6-C6", 0, "=IF(ABS(D6)<=E6,\"OK\",\"FAIL\")", "官方980022样本数"],
|
["进入重仓统计股票数", "=COUNTIF('明细'!$E$5:$E$82,\">0\")", data.filter((x) => x.fundCount > 0).length, "=B7-C7", 0, "=IF(ABS(D7)<=E7,\"OK\",\"FAIL\")", "来源匹配检查"],
|
["期末持仓市值合计(亿元)", "=SUM('明细'!$G$5:$G$82)", data.reduce((s, x) => s + x.holdValueYi, 0), "=B8-C8", 0.01, "=IF(ABS(D8)<=E8,\"OK\",\"FAIL\")", "公式汇总与源数据汇总比对"],
|
["主动+指数关系加总", "=SUM('明细'!$P$5:$Q$82)", "=SUM('明细'!$E$5:$E$82)", "=B9-C9", 0, "=IF(ABS(D9)<=E9,\"OK\",\"FAIL\")", "分类关系数必须等于原总基金数"],
|
["基金级明细行数", `=COUNTA('基金级明细'!$D$5:$D$${fundDetailLastRow})`, "=SUM('明细'!$E$5:$E$82)", "=B10-C10", 0, "=IF(ABS(D10)<=E10,\"OK\",\"FAIL\")", "每条股票-基金关系对应一行"],
|
["主动+指数市值加总(亿元)", "=SUM('明细'!$R$5:$S$82)", "=SUM('明细'!$G$5:$G$82)", "=B11-C11", 0.02, "=IF(ABS(D11)<=E11,\"OK\",\"FAIL\")", "分类市值与原汇总市值比对"],
|
["基金级明细市值加总(亿元)", `=SUM('基金级明细'!$J$5:$J$${fundDetailLastRow})`, "=SUM('明细'!$G$5:$G$82)", "=B12-C12", 0.02, "=IF(ABS(D12)<=E12,\"OK\",\"FAIL\")", "逐基金市值与原汇总市值比对"],
|
["持有基金数非负", "=MIN('明细'!$E$5:$E$82)", 0, "=B13-C13", 0, "=IF(B13>=C13,\"OK\",\"FAIL\")", "基础合理性"],
|
["占总股本比例上限", "=MAX('明细'!$H$5:$H$82)", 1, "=MAX(B14-C14,0)", 0, "=IF(B14<=C14,\"OK\",\"FAIL\")", "比例应不超过100%"],
|
];
|
for (let i = 0; i < checkRows.length; i++) {
|
const row = 4 + i;
|
const [label, actualFormula, expected, diffFormula, tolerance, statusFormula, note] = checkRows[i];
|
checks.getRange(`A${row}:G${row}`).values = [[label, null, typeof expected === "string" && expected.startsWith("=") ? null : expected, null, tolerance, null, note]];
|
checks.getRange(`B${row}`).formulas = [[actualFormula]];
|
if (typeof expected === "string" && expected.startsWith("=")) checks.getRange(`C${row}`).formulas = [[expected]];
|
checks.getRange(`D${row}`).formulas = [[diffFormula]];
|
checks.getRange(`F${row}`).formulas = [[statusFormula]];
|
}
|
const checkLastRow = checkRows.length + 3;
|
const overallCheckRow = checkLastRow + 2;
|
checks.getRange(`B4:E${checkLastRow}`).format.numberFormat = "#,##0.00;[Red](#,##0.00);-";
|
checks.getRange(`F4:F${checkLastRow}`).conditionalFormats.add("containsText", { text: "OK", format: { fill: green, font: { bold: true, color: "#006100" } } });
|
checks.getRange(`F4:F${checkLastRow}`).conditionalFormats.add("containsText", { text: "FAIL", format: { fill: red, font: { bold: true, color: "#9C0006" } } });
|
checks.mergeCells(`A${overallCheckRow}:E${overallCheckRow}`);
|
checks.getRange(`A${overallCheckRow}`).values = [["总体状态"]];
|
checks.mergeCells(`F${overallCheckRow}:G${overallCheckRow}`);
|
checks.getRange(`F${overallCheckRow}`).formulas = [[`=IF(COUNTIF(F4:F${checkLastRow},\"FAIL\")=0,\"OK\",\"FAIL\")`]];
|
checks.getRange(`A${overallCheckRow}:E${overallCheckRow}`).format = { fill: lightBlue, font: { bold: true, color: navy } };
|
checks.getRange(`F${overallCheckRow}:G${overallCheckRow}`).format = { font: { bold: true, size: 14 }, horizontalAlignment: "center" };
|
checks.getRange(`F${overallCheckRow}:G${overallCheckRow}`).conditionalFormats.add("containsText", { text: "OK", format: { fill: green, font: { bold: true, color: "#006100" } } });
|
checks.getRange(`F${overallCheckRow}:G${overallCheckRow}`).conditionalFormats.add("containsText", { text: "FAIL", format: { fill: red, font: { bold: true, color: "#9C0006" } } });
|
const checkWidths = { A: 28, B: 18, C: 18, D: 14, E: 12, F: 12, G: 45 };
|
for (const [col, width] of Object.entries(checkWidths)) checks.getRange(`${col}:${col}`).format.columnWidth = width;
|
checks.getRange(`A1:G${overallCheckRow}`).format.font = { name: "Microsoft YaHei", size: 10 };
|
checks.freezePanes.freezeRows(3);
|
|
// 全局字体与导出前验证
|
summary.getRange("A1:W81").format.font = { name: "Microsoft YaHei" };
|
await fs.mkdir(outputDir, { recursive: true });
|
console.log("CLASSIFICATION_SUMMARY");
|
console.log(JSON.stringify({
|
relations: fundRelations.length,
|
activeRelations: fundRelations.filter((x) => x.managementType === "主动型").length,
|
indexRelations: fundRelations.filter((x) => x.managementType === "指数型").length,
|
uniqueActiveFunds: uniqueActiveFunds.size,
|
uniqueIndexFunds: uniqueIndexFunds.size,
|
activeValueYi: data.reduce((sum, x) => sum + x.activeHoldValueYi, 0),
|
indexValueYi: data.reduce((sum, x) => sum + x.indexHoldValueYi, 0),
|
totalValueYi: data.reduce((sum, x) => sum + x.holdValueYi, 0),
|
unmatchedFundCatalogRows: fundRelations.filter((x) => x.fundType === "未匹配").length,
|
}));
|
const summaryInspect = await workbook.inspect({ kind: "table", range: "摘要!A1:J16", include: "values,formulas", tableMaxRows: 16, tableMaxCols: 10, maxChars: 7000 });
|
console.log("SUMMARY_INSPECT");
|
console.log(summaryInspect.ndjson);
|
const summaryTailInspect = await workbook.inspect({ kind: "table", range: "摘要!A55:J63", include: "values,formulas", tableMaxRows: 9, tableMaxCols: 10, maxChars: 6000 });
|
console.log("SUMMARY_TAIL_INSPECT");
|
console.log(summaryTailInspect.ndjson);
|
const errorScan = await workbook.inspect({ kind: "match", searchTerm: "#REF!|#DIV/0!|#VALUE!|#NAME\\?|#N/A", options: { useRegex: true, maxResults: 300 }, summary: "final formula error scan" });
|
console.log("ERROR_SCAN");
|
console.log(errorScan.ndjson);
|
const fundDetailInspect = await workbook.inspect({ kind: "table", range: "基金级明细!A1:N10", include: "values,formulas", tableMaxRows: 10, tableMaxCols: 14, maxChars: 7000 });
|
console.log("FUND_DETAIL_INSPECT");
|
console.log(fundDetailInspect.ndjson);
|
const checksInspect = await workbook.inspect({ kind: "table", range: `检查!A3:G${overallCheckRow}`, include: "values,formulas", tableMaxRows: overallCheckRow, tableMaxCols: 7, maxChars: 7000 });
|
console.log("CHECKS_INSPECT");
|
console.log(checksInspect.ndjson);
|
const summaryPreview = await workbook.render({ sheetName: "摘要", range: "A1:W33", scale: 1.2, format: "png" });
|
await fs.writeFile(`${outputDir}/summary_preview.png`, new Uint8Array(await summaryPreview.arrayBuffer()));
|
const summaryTop50Preview = await workbook.render({ sheetName: "摘要", range: "A28:J63", scale: 1.2, format: "png" });
|
await fs.writeFile(`${outputDir}/summary_top50_preview.png`, new Uint8Array(await summaryTop50Preview.arrayBuffer()));
|
const detailPreview = await workbook.render({ sheetName: "明细", range: "A1:V25", scale: 1, format: "png" });
|
await fs.writeFile(`${outputDir}/detail_preview.png`, new Uint8Array(await detailPreview.arrayBuffer()));
|
const fundDetailPreview = await workbook.render({ sheetName: "基金级明细", range: "A1:N20", scale: 1, format: "png" });
|
await fs.writeFile(`${outputDir}/fund_detail_preview.png`, new Uint8Array(await fundDetailPreview.arrayBuffer()));
|
const sourcesPreview = await workbook.render({ sheetName: "来源与口径", range: "A1:F21", scale: 1, format: "png" });
|
await fs.writeFile(`${outputDir}/sources_preview.png`, new Uint8Array(await sourcesPreview.arrayBuffer()));
|
const checksPreview = await workbook.render({ sheetName: "检查", range: `A1:G${overallCheckRow}`, scale: 1.2, format: "png" });
|
await fs.writeFile(`${outputDir}/checks_preview.png`, new Uint8Array(await checksPreview.arrayBuffer()));
|
const xlsx = await SpreadsheetFile.exportXlsx(workbook);
|
await xlsx.save(outputPath);
|
console.log(`OUTPUT=${outputPath}`);
|
