grid-backtest / frontend /src /utils /fileParser.ts
shaun07's picture
Upload folder using huggingface_hub
61ca2b1 verified
Raw History Blame Contribute Delete
24.5 kB
/**
* 文件解析工具
* 支持Excel、CSV、JSON格式的股票数据文件解析
*/
import { convertMinuteToDaily } from './technicalIndicators';
export interface StockData {
symbol: string; // 股票代码
name: string; // 股票名称
date: string; // 日期
open: number; // 开盘价
high: number; // 最高价
low: number; // 最低价
close: number; // 收盘价
volume: number; // 成交量
amount?: number; // 成交额
}
export interface ParseResult {
success: boolean;
data?: StockData[];
symbol?: string;
symbolName?: string;
error?: string;
totalRecords?: number;
dateRange?: {
start: string;
end: string;
};
dataType?: 'minute' | 'daily'; // 数据类型:分钟线或日线
}
function inferSymbolFromFilename(fileName: string): string {
try {
const base = String(fileName || '').split(/[\\/]/).pop() || ''
const name0 = base.toLowerCase().replace(/\.day$/i, '')
const digits = (name0.match(/\d{6}/g) || []).pop()
return String(digits || '').trim()
} catch {
return ''
}
}
export async function parseTDXDay(file: File): Promise<ParseResult> {
try {
const buf = await file.arrayBuffer()
const dv = new DataView(buf)
const recordSize = 32
const n = Math.floor(dv.byteLength / recordSize)
if (n <= 0) {
return { success: false, error: '文件中没有数据' }
}
const candidateScales = [10, 100, 1000, 10000]
const scores: Record<number, number> = {}
for (const s of candidateScales) scores[s] = 0
const sampleSize = Math.min(80, n)
for (let i = 0; i < sampleSize; i++) {
const off = i * recordSize
const closeRaw = dv.getUint32(off + 16, true)
const amount = dv.getFloat32(off + 20, true)
const volRaw = dv.getUint32(off + 24, true)
if (!closeRaw || !amount || !volRaw) continue
const priceImp = amount / volRaw
for (const scale of candidateScales) {
const close = closeRaw / scale
if (close <= 0) continue
const diffRatio = Math.abs(priceImp - close) / close
if (diffRatio <= 0.5) scores[scale] += 1
}
}
let scale = 100
const allZero = candidateScales.every((s) => (scores[s] || 0) === 0)
if (!allZero) {
const prefOrder = [100, 10, 1000, 10000]
scale = candidateScales
.slice()
.sort((a, b) => {
const sa = scores[a] || 0
const sb = scores[b] || 0
if (sa !== sb) return sb - sa
return prefOrder.indexOf(a) - prefOrder.indexOf(b)
})[0]
}
const symbol = inferSymbolFromFilename(file.name)
const data: StockData[] = []
for (let i = 0; i < n; i++) {
const off = i * recordSize
const dateInt = dv.getUint32(off + 0, true)
const openRaw = dv.getUint32(off + 4, true)
const highRaw = dv.getUint32(off + 8, true)
const lowRaw = dv.getUint32(off + 12, true)
const closeRaw = dv.getUint32(off + 16, true)
const amount = dv.getFloat32(off + 20, true)
const volRaw = dv.getUint32(off + 24, true)
if (!dateInt || !closeRaw) continue
const ds = String(dateInt)
if (ds.length !== 8) continue
const date = `${ds.slice(0, 4)}-${ds.slice(4, 6)}-${ds.slice(6, 8)}`
const open = Math.round((openRaw / scale) * 10000) / 10000
const high = Math.round((highRaw / scale) * 10000) / 10000
const low = Math.round((lowRaw / scale) * 10000) / 10000
const close = Math.round((closeRaw / scale) * 10000) / 10000
if (!(close > 0)) continue
data.push({
symbol,
name: '',
date,
open,
high,
low,
close,
volume: Number(volRaw || 0),
amount: Number(amount || 0),
})
}
if (data.length === 0) {
return { success: false, error: '未找到有效的股票数据,请检查数据格式' }
}
data.sort((a, b) => new Date(a.date).getTime() - new Date(b.date).getTime())
return {
success: true,
data,
symbol,
symbolName: '',
totalRecords: data.length,
dateRange: {
start: data[0].date,
end: data[data.length - 1].date,
},
dataType: 'daily',
}
} catch (error) {
return { success: false, error: `DAY解析失败: ${error}` }
}
}
/**
* 检测数据是否为分钟线数据
* @param data 股票数据数组
* @returns 如果是分钟线数据返回true,否则返回false
*/
function detectMinuteData(data: StockData[]): boolean {
if (!data || data.length < 2) return false;
// 检查日期格式是否包含时间部分
const hasTimeInDate = data.some(item => {
const dateStr = item.date;
return dateStr.includes(' ') || dateStr.includes(':') || dateStr.includes('T');
});
if (hasTimeInDate) {
return true;
}
// 检查同一天是否有多个数据点
const dateCount: Record<string, number> = {};
data.forEach(item => {
const dateOnly = item.date.split(' ')[0] || item.date;
dateCount[dateOnly] = (dateCount[dateOnly] || 0) + 1;
});
// 如果同一天的数据点超过1个,则认为是分钟线数据
return Object.values(dateCount).some(count => count > 1);
}
function convertMinuteStockToDaily(data: StockData[]): StockData[] {
const symbol = data?.[0]?.symbol || ''
const name = data?.[0]?.name || ''
return convertMinuteToDaily(data).map(d => ({
symbol,
name,
date: d.date,
open: d.open,
high: d.high,
low: d.low,
close: d.close,
volume: d.volume || 0,
}))
}
/**
* 解析CSV文件
*/
export async function parseCSV(file: File): Promise<ParseResult> {
try {
const text = await file.text();
// 尝试不同的分隔符
const separators = [',', '\t', '|', ';'];
let bestSeparator = ',';
let maxColumns = 0;
for (const sep of separators) {
const testLine = text.split('\n')[0];
const columns = testLine.split(sep).length;
if (columns > maxColumns) {
maxColumns = columns;
bestSeparator = sep;
}
}
const lines = text.split('\n').filter(line => line.trim());
if (lines.length < 2) {
return { success: false, error: '文件内容不足,至少需要标题行和一行数据' };
}
// 自动定位标题行(有些导出会在前几行加说明/空行)
let headerLineIndex = 0
for (let i = 0; i < Math.min(10, lines.length); i++) {
const hs = lines[i].split(bestSeparator).map(h => String(h || '').trim())
const joined = hs.join(' ').toLowerCase()
if (joined.includes('日期') && (joined.includes('收盘') || joined.includes('close'))) {
headerLineIndex = i
break
}
if (joined.includes('date') && (joined.includes('close') || joined.includes('收盘'))) {
headerLineIndex = i
break
}
}
// 解析标题行
const headers = lines[headerLineIndex].split(bestSeparator).map(h => h.trim());
// 查找关键列的索引
const columnMap = findColumnIndices(headers);
// 如果没有找到标准列名,尝试按位置推断(适用于您的数据格式)
if (columnMap.symbol === undefined && headers.length >= 8) {
// 根据您的数据格式:代码、名称、日期、开盘价、最高价、最低价、收盘价、成交量、成交额
columnMap.symbol = 0; // 代码
columnMap.name = 1; // 名称
columnMap.date = 2; // 日期
columnMap.open = 3; // 开盘价
columnMap.high = 4; // 最高价
columnMap.low = 5; // 最低价
columnMap.close = 6; // 收盘价
columnMap.volume = 7; // 成交量
if (headers.length > 8) {
columnMap.amount = 8; // 成交额
}
}
if (columnMap.symbol === undefined) {
return { success: false, error: '未找到股票代码列,请确保文件包含股票代码信息' };
}
if (columnMap.close !== undefined) {
if (columnMap.open === undefined) columnMap.open = columnMap.close
if (columnMap.high === undefined) columnMap.high = columnMap.close
if (columnMap.low === undefined) columnMap.low = columnMap.close
}
const data: StockData[] = [];
let symbol = '';
let symbolName = '';
// 解析数据行
for (let i = headerLineIndex + 1; i < lines.length; i++) {
const values = lines[i].split(bestSeparator).map(v => v.trim());
if (values.length < Math.max(...Object.values(columnMap)) + 1) continue;
const record: StockData = {
symbol: values[columnMap.symbol] || '',
name: values[columnMap.name || 0] || '',
date: formatDate(values[columnMap.date || 0] || ''),
open: parseFloat(values[columnMap.open || 0] || '0') || 0,
high: parseFloat(values[columnMap.high || 0] || '0') || 0,
low: parseFloat(values[columnMap.low || 0] || '0') || 0,
close: parseFloat(values[columnMap.close || 0] || '0') || 0,
volume: parseFloat(values[columnMap.volume || 0] || '0') || 0,
amount: columnMap.amount ? parseFloat(values[columnMap.amount] || '0') : undefined
};
if (record.symbol && record.date && record.open > 0) {
data.push(record);
// 获取股票代码和名称
if (!symbol && record.symbol) {
symbol = record.symbol;
symbolName = record.name;
}
}
}
if (data.length === 0) {
return { success: false, error: '未找到有效的股票数据,请检查数据格式' };
}
// 按日期排序
data.sort((a, b) => new Date(a.date).getTime() - new Date(b.date).getTime());
// 检测是否为分钟线数据,如果是则转换为日线数据
let finalData = data;
let dataType: 'minute' | 'daily' = 'daily';
if (detectMinuteData(data)) {
finalData = convertMinuteStockToDaily(data);
dataType = 'minute';
}
return {
success: true,
data: finalData,
symbol,
symbolName,
totalRecords: finalData.length,
dateRange: {
start: finalData[0].date,
end: finalData[finalData.length - 1].date
},
dataType
};
} catch (error) {
return { success: false, error: `CSV解析失败: ${error}` };
}
}
/**
* 解析Excel文件
*/
export async function parseExcel(file: File): Promise<ParseResult> {
try {
// 动态导入xlsx库
const XLSX = await import('xlsx');
const arrayBuffer = await file.arrayBuffer();
// 使用raw: true禁用自动日期转换
const workbook = XLSX.read(arrayBuffer, { type: 'array', raw: true });
// 获取第一个工作表
const sheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[sheetName];
// 转换为JSON格式,使用raw: true保持原始格式
const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1, raw: true });
if (jsonData.length < 2) {
return { success: false, error: 'Excel文件内容不足,至少需要标题行和一行数据' };
}
// 自动定位标题行(有些 Excel 会在前几行有说明/空行/合并单元格)
let headerRowIndex = 0
for (let i = 0; i < Math.min(20, jsonData.length); i++) {
const row = (jsonData[i] as any[]) || []
const cells = row.map((c) => String(c ?? '').trim()).filter(Boolean)
if (cells.length < 4) continue
const joined = cells.join(' ').toLowerCase()
if (joined.includes('日期') && (joined.includes('收盘') || joined.includes('close'))) {
headerRowIndex = i
break
}
if (joined.includes('date') && (joined.includes('close') || joined.includes('收盘'))) {
headerRowIndex = i
break
}
}
const headers = ((jsonData[headerRowIndex] as any[]) || []).map(h => String(h || '').trim());
const rows = jsonData.slice(headerRowIndex + 1) as any[][];
// 调试日志:打印前几行数据
console.log('Excel解析 - 列名:', headers);
if (rows.length > 0) {
console.log('Excel解析 - 第一行数据:', rows[0]);
};
const result = parseDataRows(headers, rows);
// 如果解析成功,检测是否为分钟线数据,如果是则转换为日线数据
if (result.success && result.data) {
let finalData = result.data;
let dataType: 'minute' | 'daily' = 'daily';
if (detectMinuteData(result.data)) {
finalData = convertMinuteStockToDaily(result.data);
dataType = 'minute';
}
return {
...result,
data: finalData,
totalRecords: finalData.length,
dateRange: {
start: finalData[0].date,
end: finalData[finalData.length - 1].date
},
dataType
};
}
return result;
} catch (error) {
return {
success: false,
error: `Excel解析失败: ${error}。请确保文件格式正确。`
};
}
}
/**
* 解析JSON文件
*/
export async function parseJSON(file: File): Promise<ParseResult> {
try {
const text = await file.text();
const jsonData = JSON.parse(text);
// 支持多种JSON格式
let records: any[] = [];
if (Array.isArray(jsonData)) {
records = jsonData;
} else if (jsonData.data && Array.isArray(jsonData.data)) {
records = jsonData.data;
} else if (jsonData.records && Array.isArray(jsonData.records)) {
records = jsonData.records;
} else {
return { success: false, error: 'JSON格式不正确,需要包含数组格式的股票数据' };
}
if (records.length === 0) {
return { success: false, error: 'JSON文件中没有数据' };
}
const data: StockData[] = [];
let symbol = '';
let symbolName = '';
for (const record of records) {
const stockData: StockData = {
symbol: record.symbol || record.code || record.股票代码 || record.代码 || '',
name: record.name || record.股票名称 || record.名称 || '',
date: formatDate(record.date || record.日期 || record.时间 || ''),
open: parseFloat(record.open || record.开盘价 || record.开盘 || '0') || 0,
high: parseFloat(record.high || record.最高价 || record.最高 || '0') || 0,
low: parseFloat(record.low || record.最低价 || record.最低 || '0') || 0,
close: parseFloat(record.close || record.收盘价 || record.收盘 || '0') || 0,
volume: parseFloat(record.volume || record.成交量 || '0') || 0,
amount: parseFloat(record.amount || record.成交额 || '0') || undefined
};
if (stockData.symbol && stockData.date) {
data.push(stockData);
if (!symbol && stockData.symbol) {
symbol = stockData.symbol;
symbolName = stockData.name;
}
}
}
if (data.length === 0) {
return { success: false, error: '未找到有效的股票数据' };
}
// 按日期排序
data.sort((a, b) => new Date(a.date).getTime() - new Date(b.date).getTime());
// 检测是否为分钟线数据,如果是则转换为日线数据
let finalData = data;
let dataType: 'minute' | 'daily' = 'daily';
if (detectMinuteData(data)) {
finalData = convertMinuteStockToDaily(data);
dataType = 'minute';
}
return {
success: true,
data: finalData,
symbol,
symbolName,
totalRecords: finalData.length,
dateRange: {
start: finalData[0].date,
end: finalData[finalData.length - 1].date
},
dataType
};
} catch (error) {
return { success: false, error: `JSON解析失败: ${error}` };
}
}
/**
* 通用数据行解析函数
*/
function parseDataRows(headers: string[], rows: any[][]): ParseResult {
try {
// 查找关键列的索引
const columnMap = findColumnIndices(headers)
console.log('parseDataRows - columnMap:', columnMap);
// 仅在完全没有找到任何列时,才按位置推断
if (Object.keys(columnMap).length === 0 && headers.length >= 8) {
columnMap.symbol = 0; // 代码
columnMap.name = 1; // 名称
columnMap.date = 2; // 日期
// 跳过第3列(时间列)
columnMap.open = 4; // 开盘价
columnMap.high = 5; // 最高价
columnMap.low = 6; // 最低价
columnMap.close = 7; // 收盘价
columnMap.volume = 8; // 成交量
if (headers.length > 9) {
columnMap.amount = 9; // 成交额
}
}
if (columnMap.symbol === undefined) {
return { success: false, error: '未找到股票代码列,请确保文件包含股票代码信息' };
}
const data: StockData[] = [];
let symbol = '';
let symbolName = '';
// 解析数据行
for (const values of rows) {
if (values.length < Math.max(...Object.values(columnMap).filter(v => v !== undefined)) + 1) continue;
const record: StockData = {
symbol: String(values[columnMap.symbol] || '').trim(),
name: String(values[columnMap.name || 0] || '').trim(),
date: formatDate(values[columnMap.date || 0]),
open: parseNumericValue(values[columnMap.open || 0]),
high: parseNumericValue(values[columnMap.high || 0]),
low: parseNumericValue(values[columnMap.low || 0]),
close: parseNumericValue(values[columnMap.close || 0]),
volume: parseNumericValue(values[columnMap.volume || 0]),
amount: columnMap.amount ? parseNumericValue(values[columnMap.amount]) : undefined
};
if (record.symbol && record.date && record.open > 0) {
data.push(record);
// 获取股票代码和名称
if (!symbol && record.symbol) {
symbol = record.symbol;
symbolName = record.name;
}
}
}
if (data.length === 0) {
return { success: false, error: '未找到有效的股票数据,请检查数据格式' };
}
// 按日期排序
data.sort((a, b) => new Date(a.date).getTime() - new Date(b.date).getTime());
return {
success: true,
data,
symbol,
symbolName,
totalRecords: data.length,
dateRange: {
start: data[0].date,
end: data[data.length - 1].date
}
};
} catch (error) {
return { success: false, error: `数据解析失败: ${error}` };
}
}
/**
* 根据文件类型选择解析器
*/
export async function parseStockDataFile(file: File): Promise<ParseResult> {
const fileName = file.name.toLowerCase();
if (fileName.endsWith('.csv')) {
return parseCSV(file);
} else if (fileName.endsWith('.xlsx') || fileName.endsWith('.xls')) {
return parseExcel(file);
} else if (fileName.endsWith('.json')) {
return parseJSON(file);
} else if (fileName.endsWith('.day')) {
return parseTDXDay(file);
} else {
return { success: false, error: '不支持的文件格式,请上传股票数据文件 (CSV、Excel、JSON、DAY)' };
}
}
/**
* 查找列索引
*/
function findColumnIndices(headers: string[]): Record<string, number> {
const columnMap: Record<string, number> = {};
headers.forEach((header, index) => {
const h = header.toLowerCase().trim();
// 股票代码
if (h.includes('市场代码') || h.includes('交易所') || h.includes('market')) {
// ignore
} else if (h.includes('股票代码') || h.includes('证券代码') || h === '代码' || h === 'symbol' || h === 'code') {
// 强匹配:优先锁定真正的证券代码列
if (columnMap.symbol === undefined || h.includes('股票代码') || h.includes('证券代码') || h === '代码') {
columnMap.symbol = index;
}
} else if ((h.includes('代码') || h.includes('symbol') || h.includes('code')) && columnMap.symbol === undefined) {
// 弱匹配:仅在还没找到 symbol 时才使用
columnMap.symbol = index;
}
// 股票名称
else if (h.includes('名称') || h.includes('name') || h.includes('股票')) {
columnMap.name = index;
}
// 日期 - 强匹配优先,避免把“上市日期”等误当成交易日期
else if (h === '日期' || h === 'date') {
columnMap.date = index;
} else if (h.includes('上市日期') || h.includes('listing') || h.includes('list_date')) {
// ignore
} else if ((h.includes('日期') || h.includes('date')) && columnMap.date === undefined) {
// 弱匹配:仅在还没找到 date 时才使用
columnMap.date = index;
}
// 时间 - 如果没有找到日期列,才使用时间列
else if (h.includes('时间') && columnMap.date === undefined) {
columnMap.date = index;
}
// 开盘价 - 精确匹配,避免误匹配
else if (h === '开盘价' || h === '开盘' || h === 'open' || h.includes('open') || h.includes('开盘')) {
columnMap.open = index;
}
// 最高价
else if (h.includes('最高') || h.includes('high')) {
columnMap.high = index;
}
// 最低价
else if (h.includes('最低') || h.includes('low')) {
columnMap.low = index;
}
// 收盘价
else if (h === '收盘价' || h === '收盘' || h.includes('收盘') || h.includes('close')) {
columnMap.close = index;
}
// 成交量
else if (h.includes('成交量') || h.includes('volume') || h.includes('vol')) {
columnMap.volume = index;
}
// 成交额
else if (h.includes('成交额') || h.includes('amount') || h.includes('turnover')) {
columnMap.amount = index;
}
});
return columnMap;
}
/**
* 安全地将值转换为数字
* @param value 可以是字符串、数字或null/undefined
* @returns 转换后的数字,如果无效则返回0
*/
function parseNumericValue(value: any): number {
if (value === null || value === undefined) {
return 0;
}
if (typeof value === 'number') {
return isNaN(value) ? 0 : value;
}
if (typeof value === 'string') {
const cleaned = value
.trim()
.replace(/,/g, '')
.replace(/%/g, '')
const parsed = parseFloat(cleaned);
return isNaN(parsed) ? 0 : parsed;
}
return 0;
}
/**
* 格式化日期
*/
function formatDate(dateInput: any): string {
if (!dateInput) return '';
// 如果是Excel的序列号日期
if (typeof dateInput === 'number' && dateInput > 1 && dateInput < 100000) {
// Excel's epoch starts on 1900-01-01, but it's bugged and thinks 1900 is a leap year.
// So we subtract 25569 to get days since 1970-01-01
const excelEpoch = new Date(1899, 11, 30);
const date = new Date(excelEpoch.getTime() + dateInput * 86400000);
return date.toISOString().split('T')[0];
}
const dateStr = String(dateInput);
const date = new Date(dateStr);
if (!isNaN(date.getTime())) {
return date.toISOString().split('T')[0];
}
// 尝试其他格式
const formats = [
{ regex: /(\d{4})-(\d{1,2})-(\d{1,2})/, y: 1, m: 2, d: 3 },
{ regex: /(\d{4})\/(\d{1,2})\/(\d{1,2})/, y: 1, m: 2, d: 3 },
{ regex: /(\d{4})(\d{2})(\d{2})/, y: 1, m: 2, d: 3 },
];
for (const format of formats) {
const match = dateStr.match(format.regex);
if (match) {
const year = parseInt(match[format.y], 10);
const month = parseInt(match[format.m], 10);
const day = parseInt(match[format.d], 10);
if (year > 1900 && month > 0 && month <= 12 && day > 0 && day <= 31) {
return `${year}-${String(month).padStart(2, '0')}-${String(day).padStart(2, '0')}`;
}
}
}
return dateStr; // 返回原始字符串
}