【理财类-01-06】20260716 股票“差价”收益计算和csv统计（Python）
·
背景需求
做过了积存金的差价
https://mp.csdn.net/mp_blog/creation/editor/162901013再做股票的每日差价，降低成本
我找了一个已经清仓的股票，且只有一次全仓买入和一次等额全仓卖出
在代码第94行写入具体参数
'''
计算单只股票一次买入一次卖出的详细记录（买入价、卖出价、股数、佣金、过户费、印花费、差价净利润、收益率）
豆包、阿夏
20260715
'''
def calc_stock_profit(buy_price, sell_price, shares, stock_code):
"""
A股盈亏计算器
扣费规则：
1. 买入扣费：佣金 + 过户费
2. 卖出扣费：佣金 + 过户费 + 印花税
3. 佣金自动区分：沪市60/68=0.1843‰，深市00/30=0.1654‰；不足5元收5元，超5元按实际
4. 过户费统一标准：成交金额 * 0.001%（按截图规则）
5. 印花税：卖出单边 成交金额 * 0.05%
"""
# 校验6位股票代码
code = str(stock_code).strip()
if len(code) != 6 or not code.isdigit():
raise ValueError("股票代码必须是6位纯数字！")
code_prefix = code[:2]
# 自动判定市场+匹配固定佣金费率（无需手动输入佣金）
if code_prefix in ("60", "68"):
market = "沪市"
commission_rate = 0.0001854 # 上海佣金 0.1843‰
elif code_prefix in ("00", "30"):
market = "深市"
commission_rate = 0.0001654 # 深圳佣金 0.1654‰
else:
raise ValueError("仅支持60/68开头沪市、00/30开头深市A股代码")
transfer_rate = 0.00001 # 截图统一过户费：0.001%
buy_total = buy_price * shares
sell_total = sell_price * shares
# ========== 买入手续费（佣金+过户费） ==========
buy_comm_theory = buy_total * commission_rate
buy_comm_real = max(buy_comm_theory, 5) # 佣金最低5元门槛
buy_transfer = buy_total * transfer_rate
buy_all_fee =buy_total+ buy_comm_real + buy_transfer
# ========== 卖出手续费（佣金+过户费+印花税） ==========
sell_comm_theory = sell_total * commission_rate
sell_comm_real = max(sell_comm_theory, 5)
sell_transfer = sell_total * transfer_rate
stamp_tax = sell_total * 0.0005
sell_all_fee = sell_total-(sell_comm_real + sell_transfer + stamp_tax)
# 盈亏计算
total_cost = buy_all_fee
total_income = sell_all_fee
net_profit = total_income - total_cost
profit_ratio = net_profit / buy_total
data = {
"股票代码": stock_code,
"所属市场": market,
"固定佣金费率": f"{commission_rate * 1000:.2f}‰",
"买入成交总额(元)": round(buy_total, 2),
"买入佣金手续费(理论计算值)": round(buy_comm_theory, 2),
"买入佣金手续费(实际收取，最低5元)": round(buy_comm_real, 2),
"买入过户费(成交金额×0.001%)": round(buy_transfer, 2),
"买入合计总费用(成交金额+佣金+过户费)": round(buy_all_fee, 2),
"----------------\n"
"卖出成交总额(元)": round(sell_total, 2),
"卖出佣金(理论计算值)": round(sell_comm_theory, 2),
"卖出佣金(实际收取，最低5元)": round(sell_comm_real, 2),
"卖出过户费(成交金额×0.001%)": round(sell_transfer, 2),
"卖出印花税(成交金额×0.05%)": round(stamp_tax, 2),
"卖出合计手续费(佣金+过户费+印花税)": round(sell_all_fee, 2),
"--------------- \n"
"一买一卖总手续费": round(sell_all_fee - buy_all_fee , 2),
"净利润(元)": round(net_profit, 2),
"收益率(净利润÷买入成交总额)": f"{profit_ratio * 100:.2f}%",
}
return data
if __name__ == "__main__":
print("===== A股手续费计算器（自动区分沪深固定佣金，过户费统一0.001%）=====")
# 固定测试参数 601169沪市
# 全部参数手动输入
# stock_code = input("请输入股票代码：").strip()
# buy_price = float(input("请输入买入单价："))
# sell_price = float(input("请输入卖出单价："))
# shares = int(input("请输入成交股数："))
# comm_wan = float(input("请输入佣金万分之几（万3输入3，万2.5输入2.5）："))
stock_code = "601169"
buy_price = 5.37
sell_price = 5.39
shares = 5200
try:
result = calc_stock_profit(buy_price, sell_price, shares, stock_code)
print("\n========== 计算结果明细 ==========")
for k, v in result.items():
print(f"{k}：{v}")
except ValueError as err:
print(f"\n输入错误：{err}")终端显示的差价（净利润）与 股票账户的显示一样：79.05元
每一个数字都和股票账户详细数据做对比
所有数字都正确，说明代码的参数是正确的。
二、导入EXCEL
但是终端显示的内容是竖列，看起来不方便
所以问问如何导出EXCEL
在第90行写入代码、买入、卖出、股数
'''
计算单只股票一次买入一次卖出的详细记录（买入价、卖出价、股数、佣金、过户费、印花费、差价净利润、收益率）
结果保存到EXCEL
豆包、阿夏
20260715
'''
def calc_stock_profit(buy_price, sell_price, shares, stock_code):
"""
A股盈亏计算器
表格规则：
1. 第1行表头，单价列统一名称为「单价」
2. 第2行买入记录：单价填入买入价
3. 第3行卖出记录：单价填入卖出价
扣费规则：
1. 买入扣费：佣金 + 过户费，总投入=成交金额+佣金+过户费
2. 卖出扣费：佣金 + 过户费 + 印花税，到手金额=成交金额-全部扣费
3. 佣金自动区分：沪市60/68=0.1843‰，深市00/30=0.1654‰；不足5元收5元，超5元按实际
4. 过户费统一标准：成交金额 * 0.001%
5. 印花税：卖出单边 成交金额 * 0.05%
"""
# 校验6位股票代码
code = str(stock_code).strip()
if len(code) != 6 or not code.isdigit():
raise ValueError("股票代码必须是6位纯数字！")
code_prefix = code[:2]
# 自动判定市场+匹配固定佣金费率
if code_prefix in ("60", "68"):
market = "沪市"
commission_rate = 0.0001843 # 上海佣金 0.1843‰
elif code_prefix in ("00", "30"):
market = "深市"
commission_rate = 0.0001654 # 深圳佣金 0.1654‰
else:
raise ValueError("仅支持60/68开头沪市、00/30开头深市A股代码")
transfer_rate = 0.00001 # 统一过户费：0.001%
buy_total = buy_price * shares # 买入纯成交金额
sell_total = sell_price * shares # 卖出纯成交金额
# ========== 买入明细计算 ==========
buy_comm_theory = buy_total * commission_rate
buy_comm_real = max(buy_comm_theory, 5) # 佣金最低5元门槛
buy_transfer = buy_total * transfer_rate
buy_total_cost = buy_total + buy_comm_real + buy_transfer # 买入总投入
buy_fee_total = buy_comm_real + buy_transfer # 买入总手续费
# ========== 卖出明细计算 ==========
sell_comm_theory = sell_total * commission_rate
sell_comm_real = max(sell_comm_theory, 5)
sell_transfer = sell_total * transfer_rate
stamp_tax = sell_total * 0.0005
sell_fee_total = sell_comm_real + sell_transfer + stamp_tax # 卖出总手续费
sell_real_income = sell_total - sell_fee_total # 卖出实际到手金额
# 盈亏汇总
net_profit = sell_real_income - buy_total_cost
profit_ratio = net_profit / buy_total
# 表格数据：表头单价统一名称，买入行填买入单价，卖出行填卖出单价
table_data = [
# 第1行：表头（单价列合并统一名称）
[
"股票代码", "所属市场", "交易类型",
"单价(元)", "持股数量(股)",
"成交总额(元)", "佣金(元)", "过户费(元)", "印花税(元)",
"总金额(元)", "净利润(元)", "收益率"
],
# 第2行：买入，单价填入买入价
[
stock_code, market, "买入",
round(buy_price,2), shares,
round(buy_total,2), round(buy_comm_real,2), round(buy_transfer,2), 0,
round(buy_total_cost,2), round(net_profit,2), f"{profit_ratio*100:.2f}%"
],
# 第3行：卖出，单价填入卖出价
[
stock_code, market, "卖出",
round(sell_price,2), shares,
round(sell_total,2), round(sell_comm_real,2), round(sell_transfer,2), round(stamp_tax,2),
round(sell_real_income,2), round(net_profit,2), f"{profit_ratio*100:.2f}%"
]
]
return table_data
if __name__ == "__main__":
print("===== A股盈亏计算器（单价列统一名称，CSV表格）=====")
# 固定测试参数，可自行修改
stock_code = "601933"
buy_price = 3.20
sell_price = 3.25
shares = 16500
# 手动输入参数模式（取消注释启用）
# stock_code = input("请输入6位股票代码：").strip()
# buy_price = float(input("请输入买入单价："))
# sell_price = float(input("请输入卖出单价："))
# shares = int(input("请输入成交股数："))
try:
data = calc_stock_profit(buy_price, sell_price, shares, stock_code)
# 生成CSV文件，Excel直接打开，解决中文乱码
with open(r"D:\股票差价\20260715股票盈亏结果2.csv", "w", encoding="utf-8-sig", newline="") as f:
for row in data:
line = ",".join([str(item) for item in row]) + "\n"
f.write(line)
print("✅ 计算完成，文件【股票盈亏明细_统一单价.csv】已生成，Excel可直接打开")
print("\n表格预览：")
for line in data:
print(line)
except ValueError as err:
print(f"输入错误：{err}")
、
但是今天我做了三笔差价。
所以我需要修改第二笔的参数，修改文件名
现在有几笔，就要反复修改参数，并且制作多个EXCEL（修改文件名），太麻烦了。
三、制作多笔差价合并在一个EXCEL，统计当日差价
修改文件名
把多笔差价信息写成一个列表
'''
计算单只股票一次买入一次卖出的详细记录（买入价、卖出价、股数、佣金、过户费、印花费、差价净利润、收益率）
结果保存到EXCEL
豆包、阿夏
20260715
'''
def calc_stock_rows(buy_price, sell_price, shares, stock_code):
code = str(stock_code).strip()
if len(code) != 6 or not code.isdigit():
raise ValueError("股票代码必须是6位纯数字！")
code_prefix = code[:2]
if code_prefix in ("60", "68"):
market = "沪市"
commission_rate = 0.0001843
elif code_prefix in ("00", "30"):
market = "深市"
commission_rate = 0.0001654
else:
raise ValueError("仅支持60/68沪市、00/30深市A股代码")
transfer_rate = 0.00001
buy_total = buy_price * shares
sell_total = sell_price * shares
# 买入费用
buy_comm_theory = buy_total * commission_rate
buy_comm_real = max(buy_comm_theory, 5)
buy_transfer = buy_total * transfer_rate
buy_total_cost = buy_total + buy_comm_real + buy_transfer
# 卖出费用
sell_comm_theory = sell_total * commission_rate
sell_comm_real = max(sell_comm_theory, 5)
sell_transfer = sell_total * transfer_rate
stamp_tax = sell_total * 0.0005
sell_real_income = sell_total - (sell_comm_real + sell_transfer + stamp_tax)
net_profit = sell_real_income - buy_total_cost
profit_ratio = net_profit / buy_total
# 关键修复：开头加英文单引号，Excel强制文本，永久保留00
code_text = f"'{code}"
buy_row = [
code_text, market, "买入",
round(buy_price,2), shares,
round(buy_total,2), round(buy_comm_real,2), round(buy_transfer,2), 0,
round(buy_total_cost,2), round(net_profit,2), f"{profit_ratio*100:.2f}%"
]
sell_row = [
code_text, market, "卖出",
round(sell_price,2), shares,
round(sell_total,2), round(sell_comm_real,2), round(sell_transfer,2), round(stamp_tax,2),
round(sell_real_income,2), round(net_profit,2), f"{profit_ratio*100:.2f}%"
]
return buy_row, sell_row
if __name__ == "__main__":
output_path = r"D:\股票差价\20260715多笔股票盈亏结果.csv"
header = [
"股票代码", "所属市场", "交易类型",
"单价(元)", "持股数量(股)",
"成交总额(元)", "佣金(元)", "过户费(元)", "印花税(元)",
"总金额(元)", "净利润(元)", "收益率"
]
# 多笔交易（包含002229测试）
transactions = [
{"stock_code": "601933", "buy_price": 3.18, "sell_price": 3.20, "shares": 9600},
{"stock_code": "601933", "buy_price": 3.20, "sell_price": 3.25, "shares": 16500},
{"stock_code": "002229", "buy_price": 9.30, "sell_price": 9.50, "shares": 2000},
]
try:
with open(output_path, "w", encoding="utf-8-sig", newline="") as f:
f.write(",".join(header) + "\n")
for idx, trade in enumerate(transactions):
buy_row, sell_row = calc_stock_rows(
trade["buy_price"], trade["sell_price"], trade["shares"], trade["stock_code"]
)
f.write(",".join([str(i) for i in buy_row]) + "\n")
f.write(",".join([str(i) for i in sell_row]) + "\n")
if idx != len(transactions) - 1:
f.write("\n")
print(f"✅ {len(transactions)}笔交易写入完成，路径：{output_path}")
print("已添加前置单引号，Excel打开完整显示002229，不会丢失前导0")
except Exception as err:
print(f"写入失败：{err}")
不错，三笔全部写入同一个地方了
三笔合并差价
与股票账户的余额，差异不大（总是有几毛几分差异）
1322.37 VS 1322.12
四、把第三个代码再完善一下，把计算佣金（理论佣金）也写进去
'''
计算单只股票一次买入一次卖出的详细记录（买入价、卖出价、股数、计算佣金、实际佣金、过户费、印花费、差价净利润、收益率）
结果保存到EXCEL
豆包、阿夏
20260715
'''
def calc_stock_rows(buy_price, sell_price, shares, stock_code):
"""
计算单条交易的买入、卖出两行数据，包含理论佣金+实际佣金
"""
# 校验6位股票代码
code = str(stock_code).strip()
if len(code) != 6 or not code.isdigit():
raise ValueError("股票代码必须是6位纯数字！")
code_prefix = code[:2]
# 自动判定市场+匹配固定佣金费率
if code_prefix in ("60", "68"):
market = "沪市"
commission_rate = 0.0001843 # 上海佣金 0.1843‰
elif code_prefix in ("00", "30"):
market = "深市"
commission_rate = 0.0001654 # 深圳佣金 0.1654‰
else:
raise ValueError("仅支持60/68开头沪市、00/30开头深市A股代码")
transfer_rate = 0.00001 # 统一过户费：0.001%
buy_total = buy_price * shares # 买入纯成交金额
sell_total = sell_price * shares # 卖出纯成交金额
# ========== 买入明细计算 ==========
buy_comm_theory = buy_total * commission_rate # 理论佣金（无保底）
buy_comm_real = max(buy_comm_theory, 5) # 实际佣金（保底5元）
buy_transfer = buy_total * transfer_rate
buy_total_cost = buy_total + buy_comm_real + buy_transfer # 买入总投入
# ========== 卖出明细计算 ==========
sell_comm_theory = sell_total * commission_rate # 理论佣金（无保底）
sell_comm_real = max(sell_comm_theory, 5) # 实际佣金（保底5元）
sell_transfer = sell_total * transfer_rate
stamp_tax = sell_total * 0.0005
sell_real_income = sell_total - (sell_comm_real + sell_transfer + stamp_tax) # 卖出实际到手
# 盈亏汇总
net_profit = sell_real_income - buy_total_cost
profit_ratio = net_profit / buy_total
# 股票代码处理：前置单引号强制Excel文本格式，永久保留前导0
code_text = f"'{code}"
# 组装买入行、卖出行，新增理论佣金字段
buy_row = [
code_text, market, "买入",
round(buy_price,2), shares,
round(buy_total,2),
round(buy_comm_theory,2), # 新增：买入理论佣金
round(buy_comm_real,2), # 实际佣金
round(buy_transfer,2), 0,
round(buy_total_cost,2), round(net_profit,2), f"{profit_ratio*100:.2f}%"
]
sell_row = [
code_text, market, "卖出",
round(sell_price,2), shares,
round(sell_total,2),
round(sell_comm_theory,2), # 新增：卖出理论佣金
round(sell_comm_real,2), # 实际佣金
round(sell_transfer,2), round(stamp_tax,2),
round(sell_real_income,2), round(net_profit,2), f"{profit_ratio*100:.2f}%"
]
return buy_row, sell_row
if __name__ == "__main__":
# 输出文件路径（可修改为你的本地路径）
output_path = r"D:\股票差价\20260715多笔股票盈亏结果2.csv"
# 表头：新增「理论佣金(元)」列，字段逻辑清晰
header = [
"股票代码", "所属市场", "交易类型",
"单价(元)", "持股数量(股)",
"成交总额(元)", "理论佣金(元)", "实际佣金(元)",
"过户费(元)", "印花税(元)",
"总金额(元)", "净利润(元)", "收益率"
]
# 多笔交易列表，可无限新增
transactions = [
# 第一笔交易
{"stock_code": "601933", "buy_price": 3.18, "sell_price": 3.20, "shares": 9600},
# 第二笔交易
{"stock_code": "601933", "buy_price": 3.20, "sell_price": 3.25, "shares": 16500},
# 第三笔交易（深市002229，测试前导0）
{"stock_code": "002229", "buy_price": 9.30, "sell_price": 9.50, "shares": 2000},
]
try:
# 写入文件
with open(output_path, "w", encoding="utf-8-sig", newline="") as f:
# 写入表头
f.write(",".join(header) + "\n")
# 遍历每笔交易，依次写入
for idx, trade in enumerate(transactions):
buy_row, sell_row = calc_stock_rows(
trade["buy_price"],
trade["sell_price"],
trade["shares"],
trade["stock_code"]
)
# 写入买入行、卖出行
f.write(",".join([str(i) for i in buy_row]) + "\n")
f.write(",".join([str(i) for i in sell_row]) + "\n")
# 每笔交易之间空一行，Excel中更清晰区分
if idx != len(transactions) - 1:
f.write("\n")
print(f"✅ {len(transactions)}笔交易计算完成，已写入文件：{output_path}")
print("✅ 已新增「理论佣金」列，完整展示佣金计算值与实收值")
print("✅ 已修复股票代码前导0丢失问题，Excel打开完整显示6位代码")
except Exception as err:
print(f"文件写入失败：{err}（请检查文件路径是否存在，文件夹是否已创建）")
五、最后把第3、6、9……的利润金额合计
'''
计算单只股票一次买入一次卖出的详细记录（买入价、卖出价、股数、计算佣金、实际佣金、过户费、印花费、差价净利润、收益率）
结果保存到EXCEL,合计多笔差价总额
豆包、阿夏
20260715
'''
def calc_stock_data(buy_price, sell_price, shares, stock_code):
"""计算单条交易的买入、卖出两行完整数据，净利润列固定在L列"""
# 校验6位股票代码
code = str(stock_code).strip()
if len(code) != 6 or not code.isdigit():
raise ValueError("股票代码必须是6位纯数字！")
code_prefix = code[:2]
# 自动判定市场+匹配固定佣金费率
if code_prefix in ("60", "68"):
market = "沪市"
commission_rate = 0.0001843 # 上海佣金 0.1843‰
elif code_prefix in ("00", "30"):
market = "深市"
commission_rate = 0.0001654 # 深圳佣金 0.1654‰
else:
raise ValueError("仅支持60/68开头沪市、00/30开头深市A股代码")
transfer_rate = 0.00001 # 统一过户费：0.001%
buy_total = buy_price * shares # 买入纯成交金额
sell_total = sell_price * shares # 卖出纯成交金额
# 买入明细计算
buy_comm_theory = buy_total * commission_rate
buy_comm_real = max(buy_comm_theory, 5)
buy_transfer = buy_total * transfer_rate
buy_total_cost = buy_total + buy_comm_real + buy_transfer
# 卖出明细计算
sell_comm_theory = sell_total * commission_rate
sell_comm_real = max(sell_comm_theory, 5)
sell_transfer = sell_total * transfer_rate
stamp_tax = sell_total * 0.0005
sell_real_income = sell_total - (sell_comm_real + sell_transfer + stamp_tax)
# 盈亏汇总
net_profit = sell_real_income - buy_total_cost
profit_ratio = net_profit / buy_total
# 股票代码处理：前置单引号强制Excel文本格式，永久保留前导0
code_text = f"'{code}"
return {
"code_text": code_text,
"market": market,
"net_profit": net_profit,
"profit_ratio": profit_ratio,
# 买入行：补全15列，确保O列是第15位
"buy_row": [
code_text, market, "买入",
round(buy_price,2), shares,
round(buy_total,2), round(buy_comm_theory,2), round(buy_comm_real,2),
round(buy_transfer,2), 0,
round(buy_total_cost,2), round(net_profit,2), f"{profit_ratio*100:.2f}%",
"", ""
],
# 卖出行：补全15列，确保O列是第15位
"sell_row": [
code_text, market, "卖出",
round(sell_price,2), shares,
round(sell_total,2), round(sell_comm_theory,2), round(sell_comm_real,2),
round(sell_transfer,2), round(stamp_tax,2),
round(sell_real_income,2), round(net_profit,2), f"{profit_ratio*100:.2f}%",
"", ""
]
}
if __name__ == "__main__":
# 输出文件路径（可修改为你的本地路径）
output_path = r"D:\股票差价\20260715多笔股票盈亏汇总表合计.csv"
# 表头：15列，列顺序完全匹配，L列为净利润，O列为第15列
header = [
"股票代码", "所属市场", "交易类型",
"单价(元)", "持股数量(股)",
"成交总额(元)", "理论佣金(元)", "实际佣金(元)",
"过户费(元)", "印花税(元)",
"总金额(元)", "净利润(元)", "收益率",
"", ""
]
# 多笔交易列表（可无限新增，自动适配求和）
transactions = [
{"stock_code": "601933", "buy_price": 3.18, "sell_price": 3.20, "shares": 9600},
{"stock_code": "601933", "buy_price": 3.20, "sell_price": 3.25, "shares": 16500},
{"stock_code": "002229", "buy_price": 9.30, "sell_price": 9.50, "shares": 2000},
]
# 第一步：预计算所有交易的行号、净利润，生成求和公式
all_net_profit = []
sell_row_nums = [] # 记录所有卖出行的Excel行号，用于生成求和公式
current_row = 2 # 数据从Excel第2行开始
for trade in transactions:
trade_data = calc_stock_data(
trade["buy_price"], trade["sell_price"], trade["shares"], trade["stock_code"]
)
# 买入行占1行 → 卖出行行号=当前行+1
current_row += 1
sell_row_nums.append(current_row)
# 卖出行占1行 + 空行占1行 → 下一笔交易行号+2
current_row += 2
# 记录净利润
all_net_profit.append(trade_data["net_profit"])
# 计算净利润总和、生成Excel动态求和公式
total_profit = sum(all_net_profit)
sum_formula = f"=SUM({','.join([f'L{row}' for row in sell_row_nums])})"
# 第二步：构建完整输出行列表，彻底避免索引越界
output_rows = []
# 1. 先加入表头（第1行）
output_rows.append(header)
# 2. 加入汇总行（第2行），O列写入求和公式+总和
summary_row = [""] * 15
summary_row[14] = f"{round(total_profit,2)}"
output_rows.append(summary_row)
# 3. 依次加入每笔交易的买入行、卖出行、空行
for trade in transactions:
trade_data = calc_stock_data(
trade["buy_price"], trade["sell_price"], trade["shares"], trade["stock_code"]
)
output_rows.append(trade_data["buy_row"]) # 买入行
output_rows.append(trade_data["sell_row"]) # 卖出行
output_rows.append([""] * 15) # 交易之间空行
# 第三步：写入CSV文件
try:
with open(output_path, "w", encoding="utf-8-sig", newline="") as f:
for row in output_rows:
f.write(",".join([str(i) for i in row]) + "\n")
print(f"✅ {len(transactions)}笔交易写入完成，文件路径：{output_path}")
print(f"✅ 已自动生成求和公式到O2单元格：{sum_formula}")
print(f"✅ 所有交易净利润总和：{round(total_profit,2)} 元")
print("✅ 已修复股票代码前导0丢失问题，Excel打开完整显示6位代码")
except Exception as err:
print(f"文件写入失败：{err}（请检查文件路径是否存在，文件夹是否已创建）")
O2里面有合计数字，但是第二行变成了空行
豆包额度也每天有限
六、我希望文件名里面有差价金额合计数字
'''
计算单只股票一次买入一次卖出的详细记录（买入价、卖出价、股数、计算佣金、实际佣金、过户费、印花费、差价净利润、收益率）
结果保存到EXCEL,合计多笔差价总额，文件名有金额
豆包、阿夏
20260715
'''
def calc_stock_data(buy_price, sell_price, shares, stock_code):
"""计算单条交易的买入、卖出两行完整数据，净利润列固定在L列"""
# 校验6位股票代码
code = str(stock_code).strip()
if len(code) != 6 or not code.isdigit():
raise ValueError("股票代码必须是6位纯数字！")
code_prefix = code[:2]
# 自动判定市场+匹配固定佣金费率
if code_prefix in ("60", "68"):
market = "沪市"
commission_rate = 0.0001843 # 上海佣金 0.1843‰
elif code_prefix in ("00", "30"):
market = "深市"
commission_rate = 0.0001654 # 深圳佣金 0.1654‰
else:
raise ValueError("仅支持60/68开头沪市、00/30开头深市A股代码")
transfer_rate = 0.00001 # 统一过户费：0.001%
buy_total = buy_price * shares # 买入纯成交金额
sell_total = sell_price * shares # 卖出纯成交金额
# 买入明细计算
buy_comm_theory = buy_total * commission_rate
buy_comm_real = max(buy_comm_theory, 5)
buy_transfer = buy_total * transfer_rate
buy_total_cost = buy_total + buy_comm_real + buy_transfer
# 卖出明细计算
sell_comm_theory = sell_total * commission_rate
sell_comm_real = max(sell_comm_theory, 5)
sell_transfer = sell_total * transfer_rate
stamp_tax = sell_total * 0.0005
sell_real_income = sell_total - (sell_comm_real + sell_transfer + stamp_tax)
# 盈亏汇总
net_profit = sell_real_income - buy_total_cost
profit_ratio = net_profit / buy_total
# 股票代码处理：前置单引号强制Excel文本格式，永久保留前导0
code_text = f"'{code}"
return {
"code_text": code_text,
"market": market,
"net_profit": net_profit,
"profit_ratio": profit_ratio,
# 买入行：补全15列，确保O列是第15位
"buy_row": [
code_text, market, "买入",
round(buy_price,2), shares,
round(buy_total,2), round(buy_comm_theory,2), round(buy_comm_real,2),
round(buy_transfer,2), 0,
round(buy_total_cost,2), round(net_profit,2), f"{profit_ratio*100:.2f}%",
"", ""
],
# 卖出行：补全15列，确保O列是第15位
"sell_row": [
code_text, market, "卖出",
round(sell_price,2), shares,
round(sell_total,2), round(sell_comm_theory,2), round(sell_comm_real,2),
round(sell_transfer,2), round(stamp_tax,2),
round(sell_real_income,2), round(net_profit,2), f"{profit_ratio*100:.2f}%",
"", ""
]
}
if __name__ == "__main__":
# 输出文件路径（可修改为你的本地路径）
# 表头：15列，列顺序完全匹配，L列为净利润，O列为第15列
header = [
"股票代码", "所属市场", "交易类型",
"单价(元)", "持股数量(股)",
"成交总额(元)", "理论佣金(元)", "实际佣金(元)",
"过户费(元)", "印花税(元)",
"总金额(元)", "净利润(元)", "收益率",
"", ""
]
# 多笔交易列表（可无限新增，自动适配求和）
transactions = [
{"stock_code": "601933", "buy_price": 3.18, "sell_price": 3.20, "shares": 9600},
{"stock_code": "601933", "buy_price": 3.20, "sell_price": 3.25, "shares": 16500},
{"stock_code": "002229", "buy_price": 9.30, "sell_price": 9.50, "shares": 2000},
]
# 第一步：预计算所有交易的行号、净利润，生成求和公式
all_net_profit = []
sell_row_nums = [] # 记录所有卖出行的Excel行号，用于生成求和公式
current_row = 2 # 数据从Excel第2行开始
for trade in transactions:
trade_data = calc_stock_data(
trade["buy_price"], trade["sell_price"], trade["shares"], trade["stock_code"]
)
# 买入行占1行 → 卖出行行号=当前行+1
current_row += 1
sell_row_nums.append(current_row)
# 卖出行占1行 + 空行占1行 → 下一笔交易行号+2
current_row += 2
# 记录净利润
all_net_profit.append(trade_data["net_profit"])
# 计算净利润总和、生成Excel动态求和公式
total_profit = sum(all_net_profit)
sum_formula = f"=SUM({','.join([f'L{row}' for row in sell_row_nums])})"
# 第二步：构建完整输出行列表，彻底避免索引越界
output_rows = []
# 1. 先加入表头（第1行）
output_rows.append(header)
# 2. 加入汇总行（第2行），O列写入求和公式+总和
summary_row = [""] * 15
summary_row[14] = f"{round(total_profit,2)}"
output_rows.append(summary_row)
# 3. 依次加入每笔交易的买入行、卖出行、空行
for trade in transactions:
trade_data = calc_stock_data(
trade["buy_price"], trade["sell_price"], trade["shares"], trade["stock_code"]
)
output_rows.append(trade_data["buy_row"]) # 买入行
output_rows.append(trade_data["sell_row"]) # 卖出行
output_rows.append([""] * 15) # 交易之间空行
output_path = fr"D:\股票差价\20260715多笔股票盈亏汇总表合计({round(total_profit,2)}元）.csv"
# 第三步：写入CSV文件
try:
with open(output_path, "w", encoding="utf-8-sig", newline="") as f:
for row in output_rows:
f.write(",".join([str(i) for i in row]) + "\n")
print(f"✅ {len(transactions)}笔交易写入完成，文件路径：{output_path}")
print(f"✅ 已自动生成求和公式到O2单元格：{sum_formula}")
print(f"✅ 所有交易净利润总和：{round(total_profit,2)} 元")
print("✅ 已修复股票代码前导0丢失问题，Excel打开完整显示6位代码")
except Exception as err:
print(f"文件写入失败：{err}（请检查文件路径是否存在，文件夹是否已创建）")文件名里面有合计的差价
Agent 垂直技术社区，欢迎活跃、内容共建。
更多推荐
7
0- 0
已为社区贡献10条内容
所有评论(0)