如今电子交易已经普及，银行、券商、支付软件等交易平台通常都可以导出历史交易记录，用户一般不需要手工记录每笔交易。但是，如何把各个交易平台上格式各异的交易记录批量导入到TataruBook，是影响记账效率的主要问题。

本页提供一套交易记录数据的转换和导入方法，供用户参考。

把文件转换为Excel表格

从交易平台上导出的原始交易记录文件可能为各种格式，如文本、csv、Excel、pdf等等。如果原始交易记录是csv文件或者Excel文件，那么这一步往往可以跳过去。否则，需要先把原始交易记录文件的内容转换成Excel表格。

注意Excel表格内容要符合以下两个要求：

  • 每一行表示一笔交易；
  • 交易记录按照时间升序排列（特别是同一天内的多笔交易的顺序，因为这是按日期排序解决不了的）。

下面给出一个原始交易记录文件的例子，这个例子模拟了一段从券商导出的交易记录。你的真实交易记录文件很可能和这个例子不一样——有可能多一些列或者少一些列，列的顺序也可能不同。但是没关系，只要Excel表格符合上面两个要求，后面的步骤只需要根据情况进行调整，最终都能完成导入。

流水号 清算周期 业务名称 发生金额 剩余金额 证券代码 证券名称 长简称 成交价格 成交数量
20605 20230103 银行转存 8000 8000       0 0
506106 20230110 证券卖出 1281.17 0.61 123147 中辰转债 中辰转债 128.127 -10
11612 20230215 银行转取 -19999.97 0.03       0 0
492351 20230310 证券买入 -60076.91 0.04 513050 中概互联 中概互联网ETF 1.013 59300
546304 20230321 利息归本 2.83 2.83       0 0
744531 20230322 股息入账 2 2 113056 重银转债 重银转债 0 0
744535 20230322 兑息扣税 -0.4 1.6 113056   重银转债 0 0
501079 20230324 证券卖出 60018 0.04 512980 传媒ETF 传媒ETF 0.82 -73200

对于原始交易记录文件不是csv文件或者Excel文件的情况：

  • 如果是文本格式，那么可以利用Excel的文本导入向导将其转换成表格；
  • 如果是pdf等其他格式，通常可以把其中的文本复制出来，然后再利用Excel的文本导入向导转换成表格。

有的时候，原始交易记录文件并不符合“每一行表示一笔交易”的要求，列之间的分隔符也可能不规律。在这种情况下，可能需要使用字符批量查找替换、正则表达式、Excel公式、编写程序脚本等其他处理方法。

在这一步中需要注意检查Excel单元格中是否存在多余的空格、制表符、回车等字符，这些字符可能导致后续的处理步骤失败。而且由于这些字符在Excel中不可见，这种问题比较难以被定位。在处理原始交易记录文件时，应当尽早将这些多余的字符删除。

建立可重用的转换模板

新建一个Excel文件，并将其中第一个工作表命名为原始数据，在这个工作表中贴入上一步得到的数据。

这个Excel文件将会成为对所有这种数据格式进行处理的转换模板。以后对于这种格式的数据，只要将其贴到原始数据工作表中，就能自动完成转换。——也就是说，本文所描述的大多数步骤仅在第一次对某个交易平台的数据进行处理时才需要，一旦转换模板建立成功了，后续再处理类似数据时，大多数步骤都可以自动完成。

不同交易平台导出的数据格式可能不同，因此通常需要针对每个交易平台建立其专用的转换模板。

配置参数

在转换模板中原始数据的后面新建一个工作表，命名为参数。

然后观察原始数据中，对于每笔交易，根据什么信息可以决定交易另一方的账户。比如，对于前面的例子而言：

  • 如果业务名称列的内容是银行转存或者银行转取，那么交易另一方的账户是这个证券账户绑定的银行账户（假设例子中是萨雷安银行活期）；
  • 如果业务名称列的内容是证券卖出、证券买入、股息入账或者兑息扣税，那么交易另一方的账户是证券名称那一列所指示的投资品（股票、基金或债券等等）；
  • 如果业务名称列的内容是利息归本，那么交易另一方的账户是名为利息的外部账户。

可以看到，业务名称列的内容是判断的决定因素。当业务名称是银行转存、银行转取或者利息归本时，交易另一方的账户是一个约定的账户；否则交易另一方的账户可根据其他列的内容获取。根据这些规律，在参数工作表中输入部分业务名称和交易另一方的账户的对应关系，如下：

银行转存 萨雷安银行活期
银行转取 萨雷安银行活期
利息归本 利息

注意当业务名称不是上面这三种时，交易另一方的账户可根据原始数据中其他列的内容获取，因此不需要输入到参数工作表。

当原始数据包含很多日常消费、转账时，这个步骤的处理可能会相当个性化。比如，如何界定一笔交易是餐饮费支出？——如果你大部分时候都在某个特定餐厅就餐，那么这些交易记录可能在某列都具有一样的值，在参数工作表中可以添加某列特征与餐饮费的对应关系。但是使用这种方法往往不足以处理所有的数据。有一些特殊的消费或者收入可能需要人工干预处理。

抓取和转换需要的信息

原始数据中有一些列的信息是不需要导入到db文件的，比如例子中的流水号、剩余金额。——当然，如果你觉得有必要，也可以把一些需要保留的信息放入postings表的comment字段。但最重要的还是从原始数据中抓取交易的关键信息。

在转换模板中参数的后面新建一个工作表，命名为中间结果，在该工作表的第一行写出以下几个列名：

trade_date this_account amount other_account comment other_change

接下来，需要根据原始数据的结构，为每一列设计公式来抓取和转换数据。以工作表第二行（即数据内容的第一行）中各列的公式为例：

trade_date是交易日期。原始数据中交易日期在清算周期列（即B列）。原始数据中的交易日期是YYYYMMDD格式，需要被转换成Excel的日期格式。设计公式为：=DATE(LEFT(原始数据!B2,4),MID(原始数据!B2,5,2),RIGHT(原始数据!B2,2))。这个公式组合使用了字符串处理函数和日期构造函数。如果你的原始数据中日期格式不是这样的，那么公式需要根据情况修改。

this_account是原始数据的所有交易发生的账户，即这个交易平台对应的账户。我们假设例子中的券商账户名是莫古证券资金，将其填入this_account列。在这一列中不需要用公式，因为每笔交易的this_account值都是一样的。

如果你在同一个交易平台有多个不同账户（比如不同家庭成员在同一个券商下开的账户），由于这些账户产生的原始交易记录文件数据格式都是一样的，它们可以用同一个转换模板进行处理。在这种情况下，你最好把账户名配置为一个参数写在参数工作表中，并在this_account列引用这个参数。

amount是this_account账户在交易中产生的余额变动数量，对应原始数据中D列发生金额。这一列的公式很简单，直接引用原始数据中D列的值就行了。公式为：=原始数据!D2。

other_account是交易另一方的账户名字。根据配置参数步骤中配置好的参数，可以这样设计公式：

  • 如果原始数据中业务名称的值在参数工作表的A列出现了，那么参数工作表B列对应的值就是交易另一方的账户名字。公式为：=VLOOKUP(原始数据!C2,参数!A:B,2,FALSE)。
  • 如果原始数据中业务名称的值在参数工作表的A列没有出现过，说明交易另一方的账户名字由原始数据中证券名称列（即G列）的值指示。注意证券名称列的值不一定就是账户名，它更像是资产名。由于同一种资产可能对应多个账户，一般建议账户的命名要在资产名的基础上加一些限定信息。在我们的例子中，假设账户的命名规则是券商名_证券名称——如莫古证券_中辰转债。那么设计的公式为：=CONCAT("莫古证券_",原始数据!G2)。同样的，这里可以考虑把账户名前缀写到参数工作表中并引用。
  • 把上面两个分支的公式用IF判断函数组合起来，得到other_account列的完整公式：=IF(COUNTIF(参数!A:A,原始数据!C2)>0,VLOOKUP(原始数据!C2,参数!A:B,2,FALSE),CONCAT("莫古证券_",原始数据!G2))

comment是交易的备注信息，这一列中填什么数据取决于用户的喜好。我们的例子直接抓取业务名称放到备注中：=原始数据!C2

other_change是交易另一方的账户产生的余额变动数量，它只在部分交易中存在。在例子中它对应的是原始数据中的成交数量列（即J列）。但是观察发现，当这个值不应该存在时（比如银行转存交易），原始数据中的成交数量为0。为了不错误的填入0值，这里设计一个带有筛选条件的公式，只对于证券交易才填写other_change：=IF(COUNTIF(参数!A:A,原始数据!C2)>0,"",原始数据!J2)

按照以上过程为各列设计的公式总结如下：

列 公式
trade_date =DATE(LEFT(原始数据!B2,4),MID(原始数据!B2,5,2),RIGHT(原始数据!B2,2))
this_account 莫古证券资金
amount =原始数据!D2
other_account =IF(COUNTIF(参数!A:A,原始数据!C2)>0,VLOOKUP(原始数据!C2,参数!A:B,2,FALSE),CONCAT("莫古证券_",原始数据!G2))
comment =原始数据!C2
other_change =IF(COUNTIF(参数!A:A,原始数据!C2)>0,"",原始数据!J2)

数据内容第一行的公式填写好后，可以利用Excel的自动填充功能，把其他行都填上相应公式，并得到计算结果如下：

trade_date this_account amount other_account comment other_change
2023/1/3 莫古证券资金 8000 萨雷安银行活期 银行转存  
2023/1/10 莫古证券资金 1281.17 莫古证券_中辰转债 证券卖出 -10
2023/2/15 莫古证券资金 -19999.97 萨雷安银行活期 银行转取  
2023/3/10 莫古证券资金 -60076.91 莫古证券_中概互联 证券买入 59300
2023/3/21 莫古证券资金 2.83 利息 利息归本  
2023/3/22 莫古证券资金 2 莫古证券_重银转债 股息入账 0
2023/3/22 莫古证券资金 -0.4 莫古证券_ 兑息扣税 0
2023/3/24 莫古证券资金 60018 莫古证券_传媒ETF 证券卖出 -73200

这一步中的公式设计方法取决于你的实际数据结构，可能需要经常参考Excel函数文档。如果你对Excel公式不怎么熟悉，可以考虑使用搜索引擎，或者询问AI获取帮助。

检查中间结果的正确性

现在需要对中间结果工作表的数据进行人工检视。在上面的例子中，检视将发现一个问题：2023/3/22的兑息扣税交易，other_account的值是错误的：莫古证券_。回顾原始数据发现，在这一行中证券名称的值是空的。要解决这个问题，需要手工在原始数据的证券名称里填入重银转债，填入之后other_account就能正常显示为莫古证券_重银转债。

由于原始数据的每笔交易不一定总是严格遵循一致规则，有时候数据会存在一些异常情况，因此需要人工检视和处理。但是注意：修改数据时，只应该修改原始数据工作表，不要修改中间结果工作表，否则将会破坏编写好的公式，使得转换模板不能再被重用。

选择源账户和目标账户

在转换模板中中间结果的后面新建一个工作表，命名为最终结果，在该工作表的第一行，按照postings表的导入要求，写出以下几个列名：

posting_index trade_date src_account src_change dst_account comment dst_change

dst_change是posting_extras表的字段而不是postings表的字段，但是由于自动插入关联表的记录功能，可以让TataruBook同时在这两个表中插入关联的数据，因此交易记录导入时需要这一列。

在最终结果中，posting_index列留为空（原因见自动生成的索引字段），trade_date、comment列的数据直接引用中间结果中对应列的数据即可。而src_account、src_change、dst_account、dst_change这四列的内容，需要根据每笔交易资金的流动方向来确定。

观察中间结果，可以发现如果amount的数值小于\(0\)，那么src_account（即资金流出的账户）是this_account，而dst_account（即资金流入的账户）是other_account；如果amount列的数值大于\(0\)，那么src_account是other_account，而dst_account是this_account。也就是说，需要根据交易的余额变动数量来判断是否交换两个账户。

但是，如果amount等于\(0\)时怎么办？在例子中并没有交易记录的amount为\(0\)，但实际上，如果出现了送股、缩股，是可能出现对应交易中仅股份数量变化，而交易资金为\(0\)的。为了让转换模板能适应这些场景，最好的做法是：如果amount为\(0\)，再进一步根据other_change值为正数还是负数来确定资金的流动方向。

对于src_change、dst_change两列，当中间结果中other_change的值存在时，它们和src_account、dst_account的计算方法是类似的，可以利用src_account计算的结果来判断是否交换这两个余额变动数量。但是如果中间结果中other_change是空的，那么最终结果中dst_change列也应当为空，而src_change需要判断是取amount的值还是amount的相反数（因为src_change总是小于等于\(0\)）。

综上所述，最终结果工作表的第二行（即数据内容的第一行）各列的公式为：

列 公式
posting_index  
trade_date =中间结果!A2
src_account =IF(中间结果!C2<0,中间结果!B2,IF(中间结果!C2>0,中间结果!D2,IF(中间结果!F2<0,中间结果!D2,中间结果!B2)))
src_change =IF(中间结果!F2="",IF(中间结果!B2=C2,中间结果!C2,-中间结果!C2),IF(中间结果!B2=C2,中间结果!C2,中间结果!F2))
dst_account =IF(中间结果!B2=C2,中间结果!D2,中间结果!B2)
comment =中间结果!E2
dst_change =IF(中间结果!F2="","",IF(中间结果!B2=C2,中间结果!F2,中间结果!C2))

虽然这些公式看起来有点复杂，但是最终结果工作表的公式几乎可以在任何转换模板中复用。只要中间结果工作表是严格按照前面步骤生成的固定格式，那么最终结果工作表的所有公式都可以直接从已有的转换模板中复制。

将这些公式填充到所有的行，得到计算结果如下：

posting_index trade_date src_account src_change dst_account comment dst_change
  2023/1/3 萨雷安银行活期 -8000 莫古证券资金 银行转存  
  2023/1/10 莫古证券_中辰转债 -10 莫古证券资金 证券卖出 1281.17
  2023/2/15 莫古证券资金 -19999.97 萨雷安银行活期 银行转取  
  2023/3/10 莫古证券资金 -60076.91 莫古证券_中概互联 证券买入 59300
  2023/3/21 利息 -2.83 莫古证券资金 利息归本  
  2023/3/22 莫古证券_重银转债 0 莫古证券资金 股息入账 2
  2023/3/22 莫古证券资金 -0.4 莫古证券_重银转债 兑息扣税 0
  2023/3/24 莫古证券_传媒ETF -73200 莫古证券资金 证券卖出 60018

识别已经导入过的交易记录

在上面的例子中，第一笔交易银行转存是从萨雷安银行活期账户转账\(8000\)到莫古证券资金账户。正常情况下，这条交易记录在交易双方的两个账户中会同时存在。如果我们先导入了萨雷安银行活期的交易记录，那么在导入莫古证券资金的交易记录之前，这条记录就已经在db文件中了。因此，在导入莫古证券资金的交易记录时，需要先识别出这些已经导入过的交易记录，避免这些记录在db文件中重复多次。

这个步骤不是必须的，如果你确信需要导入的交易记录在db文件中不存在，那么可以跳过这个步骤。

在最终结果工作表的后面新建一个工作表，命名为statements。

使用export命令将db文件的statements视图内容导出，然后用Excel打开，对src_name列筛选，只显示莫古证券资金账户的记录。观察与当前要导入的交易记录有重合的时间段，把这段时间的交易记录复制到statements工作表中。

接下来，我们需要对中间结果工作表和statements工作表中的交易记录进行一对一的匹配。正确的记账数据应当满足这样的匹配结果：

  • 当两条记录匹配时，它们的交易日期和交易金额相同。（注*）
  • 对于statements工作表中的每一条记录，在中间结果工作表中都有唯一一条记录与之匹配。
  • 如果某个账户这段时间内的交易记录已经导入过了，那么对于中间结果工作表中和这个账户发生的每一笔交易，在statements工作表中都要有唯一一条记录与之匹配。

注*：现实中的转账、汇款等交易有可能不是当天到账，也可能因为手续费等原因使得转出金额与转入金额不同。在这些情况下，你需要对转账交易做一些手工处理。比如：把一笔转账交易拆分为支付手续费和实际转账两笔交易；或者新增一个“在途”账户，让资金先转入“在途”账户，再转入目标账户。

我们使用一个新构造的例子来展示这个匹配过程。假设中间结果工作表的内容如下：

trade_date this_account amount other_account comment other_change
2023/1/3 莫古证券资金 8000 莫古证券_加隆德炼铁厂 证券卖出 400
2023/1/3 莫古证券资金 8000 萨雷安银行活期 银行转存  
2023/1/3 莫古证券资金 8000 萨雷安银行活期 银行转存  

statements工作表的内容如下：

posting_index trade_date account_index amount target comment src_name asset_index is_external target_name balance
102 2023/1/3 1 8000 3 银证转账 莫古证券资金 1 0 萨雷安银行活期 18000
103 2023/1/3 1 8000 3 银证转账 莫古证券资金 1 0 萨雷安银行活期 26000

在这个例子中，statements中的两条记录（第二行和第三行）应该分别匹配中间结果中的后两条记录（即第三行和第四行）。但问题是：中间结果的三条记录的交易日期和交易金额都是一样的，如果用普通的VLOOKUP函数来匹配，会导致结果不正确。因此，下面会使用一些比较特殊的公式。

首先在中间结果当前最后一列other_change（F列）的右边新增一列key_info（G列），在第二行（即数据的第一行）填入公式：=CONCAT(A2," ",C2)。在statements当前最后一列balance（K列）的右边新增一列key_info（L列），在第二行（即数据的第一行）填入公式：=CONCAT(B2," ",D2)。这两个公式的作用都是把交易日期和交易金额两个字段拼接起来，方便后续处理。你可能会看到拼接后的日期显示为一个整数，这是因为Excel在表示日期时内部使用的是整数，这对于处理不会有影响。

然后在中间结果的key_info列（G列）右边新增一列force_align（H列）。这一列没有公式，暂时为空，它用于在必要时允许用户手工将某条记录匹配到statements中的一条记录。

接着在statements的key_info列（L列）右边新增一列（M列），在M列的第一行（表头那一行）输入数字1；第二行输入公式：=IF(COUNTIF(中间结果!H:H,ROW())>0,MATCH(ROW(),中间结果!H:H,0),MATCH(L2,OFFSET(中间结果!G$1,M1,0,9999),0)+M1)

这条公式先判断中间结果的force_align列（H列）中有没有手工指定匹配，如果有，则直接使用指定的匹配。否则，在中间结果中，从上一条已经匹配过的记录往下，查找与statements该行记录相匹配的记录。这样的处理方式保证了不会产生两条中间结果记录匹配同一条statements记录。

把上面这些公式使用Excel的自动填充功能填充到所有的行。然后可以看到statements的M列中，对第二行和第三行的两条记录分别找到的匹配是2和3，也就是中间结果的前两条记录。

这个匹配关系是不正确的，因为中间结果的第一条记录不应该参与到匹配，但是由于第一条记录的交易日期和交易金额恰好和需要匹配的记录一样，导致它先被匹配上。这种情况就需要手工干预：在中间结果工作表的H3单元格中输入数字2，表示指定中间结果的这条记录匹配statements第二行的记录。

修改H3单元格的内容之后，statements的M列内容发生了更新：现在两条记录分别找到的匹配是3和4，即中间结果工作表的第三行和第四行，匹配结果正确。

最后，在最终结果工作表中当前最后一列dst_change（G列）的右边新增一列skip（H列），在第二行输入公式：=COUNTIF(statements!M:M,ROW())。这条公式用于显示该行的记录是否在statements中有匹配，如果有，结果为1，否则为0。填充该公式到所有行，并筛选这一列为0的记录，这些就是需要导入的，db文件中不存在的交易记录。

按照以上过程为相关工作表添加的列及每列第二行的公式总结如下：

工作表 列名 列序号 公式
中间结果 key_info G =CONCAT(A2," ",C2)
中间结果 force_align H 无公式，用于手工指定匹配
statements key_info L =CONCAT(B2," ",D2)
statements 1 M =IF(COUNTIF(中间结果!H:H,ROW())>0,MATCH(ROW(),中间结果!H:H,0),MATCH(L2,OFFSET(中间结果!G$1,M1,0,9999),0)+M1)
最终结果 skip H =COUNTIF(statements!M:M,ROW())

保存为csv文件并导入数据

最终结果工作表中的数据已经符合TataruBook的交易记录导入要求了。把这些数据保存成postings.csv文件并使用import命令导入，所有的工作就完成了。

但是要注意：有一些交易记录所涉及的账户可能在TataruBook中还没有创建。比如例子中的证券买入如果是第一次买入中概互联网ETF这个证券品种，那么有可能在导入时accounts表和asset_types表中找不到这个品种，导致导入失败。不过，TataruBook在任一条记录导入失败时会触发自动回滚而不影响已有数据。因此可以多次尝试导入，并根据每次提示的失败信息补充accounts表和asset_types表的记录，直到整个postings.csv文件导入成功。在转换模板的中间结果工作表中，可以新增几列来抓取accounts表和asset_types表所需要的字段，用于当导入失败时在这两个表中添加需要的记录。