我的客户在Excel中有许多不同的经纪帐户。对于每个帐户,它都有一个帐户名称,帐户类型,股票名称以及每个股票的分配。每个帐户中的股票数量各不相同。该文件如下所示:
Account Name Account Type Stock Name Stock Allocation
MN001 #1 ABC 40%
MN001 #1 ABD 60%
MN002 #2 ABC 50%
MN002 #2 ABD 40%
MN002 #2 EFG 10%
MN003 #3 ABC 20%
MN003 #3 ABD 40%
MN003 #3 EFG 40%
MN004 #4 ABC 30%
MN004 #4 ABD 70%
我们要从帐户类型#1(MN001)移至其他帐户类型(MN002,MN003或MN004)。标准是找到营业额最少的帐户。例如,MN001和MN002之间的周转率是ABC(0.1)+ ABD(0.2)+ EFG(0.1)= 0.4。 MN001和MN003之间的周转率是ABC(0.2)+ ABD(0.2)+ EFG(0.4)= 0.8。 MN001和MN004之间的周转率是ABC(0.1)+ ABD(0.1)= 0.2。 MN004的营业额最低。
因此,所需的输出:
From Account/Account Type To Account/Account Type Minimum TurnOver
MN001 #1 MN004 #4 0.2
MN002 #2 MN001 #1 0.4
在Excel中似乎很难做到这一点。可以用Python完成吗?非常感谢您的帮助!
最佳答案
是的,它可以用Python完成,看起来可能像这样。
accounts = [{'ABC':0.4, 'ABD':0.6}, {'ABC':0.5, 'ABD':0.4, 'EFG':0.1}]
这是您的帐户,MN001是
accounts[0]
,MN002是accounts[1]
,依此类推。def compute_turnover(first_account, second_account):
turnover = 0
for stock_name, stock_allocation in first_account.items():
other_allocation = second_account.get(stock_name)
if other_allocation is not None:
turnover += abs(stock_allocation - second_account[stock_name])
else:
turnover += stock_allocation
for stock_name, stock_allocation in second_account.items():
other_allocation = first_account.get(stock_name)
if other_allocation is None:
turnover += stock_allocation
return turnover
如上定义一个compute_turnover函数,用MN001和MN002调用它,您将获得预期的结果
>>> compute_turnover(accounts[0], accounts[1])
0.4
现在,假设您有一个需要转移的帐户的
list1
和一个目标帐户的list2
。您只需要在python字典中转换两个列表,然后按以下方式进行迭代list1 = [{'ABC':0.4, 'ABD':0.6}, {'ABC':0.5, 'ABD':0.4, 'EFG':0.1}]
list2 = [{'ABC':0.4, 'ABD':0.6}, {'ABC':0.5, 'ABD':0.4, 'EFG':0.1}]
turnover_accounts = []
for account in list1:
min_turnover = 1e9
turnover_account = None
for target_account in list2:
if compute_turnover(account, target_account) < min_turnover:
min_turnover = compute_turnover(account, target_account)
turnover_account = target_account
turnover_accounts.append(turnover_account)
您的
turnover_accounts
列表将具有最低营业额的目标帐户的索引。关于python - 查找最低营业额账户,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/54931074/