我的客户在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/

10-12 22:03