当前位置： 文江博客话题详情

根据数量和价格调整股票投资组合的价格

发布于 2025-01-17 04:58:18 字数 3548 浏览 2 评论 0原文

我正在尝试调整我的股票投资组合并尝试计算调整后的平均价格（这是加权平均的一种形式）。

以下是示例数据：


import pandas as pd 
import numpy as np

sample_dict = {'ticker': {1: 'ABCD',
  2: 'ABCD', 3: 'ABCD', 4: 'ABCD', 5: 'ABCD', 6: 'ABCD', 8: 'EFGH',
  9: 'EFGH', 10: 'EFGH', 11: 'EFGH', 12: 'EFGH', 13: 'EFGH'},
 'Date': {1: "2018, 1, 10",   2: "2018, 1, 20",
  3: "2018, 2, 7", 4: "2018, 4, 14",
  5: "2018, 5, 25", 6: "2018, 7, 4",
  8: "2018, 1, 10", 9: "2018, 1, 20",
  10: "2018, 2, 7", 11: "2018, 4, 14",
  12: "2018, 5, 25", 13: "2018, 7, 4"},
 'Sell_buy': {1: 'buy', 2: 'buy',  3: 'sell', 4: 'buy', 5: 'sell', 6: 'buy',
  8: 'buy', 9: 'buy', 10: 'buy', 11: 'buy', 12: 'sell', 13: 'sell'},
 'Qtd': {1: 100.0, 2: 300.0, 3: 200.0, 4: 500.0, 5: 600.0, 6: 500.0,
  8: 300.0, 9: 300.0, 10: 200.0, 11: 200.0, 12: 700.0, 13: 100.0},
 'Price': {1: 8.0, 2: 10.0, 3: 12.0, 4: 9.0, 5: 13.0, 6: 14.0,
  8: 8.0, 9: 10.0, 10: 12.0, 11: 9.0, 12: 13.0, 13: 14.0},
 'Costs': {1: 10.93, 2: 12.52, 3: 11.39, 4: 14.5, 5: 14.68, 6: 14.96,
  8: 10.93, 9: 12.52, 10: 11.39, 11: 14.5, 12: 14.68, 13: 14.96}}

sample_df = pd.DataFrame(sample_dict)
sample_df['Date']=pd.to_datetime(sample_df['Date'], dayfirst=True).dt.date

我能够毫无问题地获得更新的调整数量（基于买卖）：


#to calculate adjusted quantity. this works as expected
sample_df['Adj Qtd'] = sample_df.apply(lambda x: ((x.Sell_buy == "buy") - (x.Sell_buy == "sell")) * x['Qtd'], axis = 1)
sample_df['Adj Qtd'] = sample_df.groupby('ticker')['Adj Qtd'].cumsum()

但是，我无法获得正确的调整价格。 这里的条件是，如果我卖出一只股票，我的调整价格不应改变，并保持与买入该股票时的最后调整价格相同。

我已经尝试了以下方法来实现这一目标：

#Adjust Price. Works good until I reach the row where a sell was made
sample_df['Adjusted Price'] = sample_df.apply(lambda x: ((x.Sell_buy == "buy") - (x.Sell_buy == "sell")) * (x["Price"] * x["Qtd"] + x["Costs"]), axis = 1)
sample_df['Adjusted Price'] = sample_df.groupby('ticker')['Adjusted Price'].cumsum().div(sample_df['Adj Qtd'])

我可以使用以下方法部分纠正卖出行的调整价格：

# When it's a "sell", adjusted price is the same from above
sample_df.loc[sample_df['Sell_buy'] == 'sell',['Adjusted Price']] = np.NaN
sample_df.fillna(method='ffill', inplace=True)

    ticker  Date    Sell_buy        Qtd Price   Costs   Adj Qtd Adjusted Price
1   ABCD    2018-10-01  buy       100.0 8.0     10.93   100.0   8.109300
2   ABCD    2018-01-20  buy       300.0 10.0    12.52   400.0   9.558625
3   ABCD    2018-07-02  sell      200.0 12.0    11.39   200.0   9.558625
4   ABCD    2018-04-14  buy       500.0 9.0     14.50   700.0   8.466514
5   ABCD    2018-05-25  sell      600.0 13.0    14.68   100.0   8.466514
6   ABCD    2018-04-07  buy       500.0 14.0    14.96   600.0   8.544733
8   EFGH    2018-10-01  buy       300.0 8.0     10.93   300.0   8.036433
9   EFGH    2018-01-20  buy       300.0 10.0    12.52   600.0   9.039083
10  EFGH    2018-07-02  buy       200.0 12.0    11.39   800.0   9.793550
11  EFGH    2018-04-14  buy       200.0 9.0     14.50   1000.0  9.649340
12  EFGH    2018-05-25  sell      700.0 13.0    14.68   300.0   9.649340
13  EFGH    2018-04-07  sell      100.0 14.0    14.96   200.0   9.649340

如果“买入”之间没有“卖出”（就像它对股票 EFGH 所做的那样），这将完美地工作这个例子）。 需要明确的是，当交易是“卖出”时，我们必须忽略调整价格，并使用该特定股票的最后一次买入类型交易中的最后调整价格。

我在 Excel 中完成了所有这些微积分，输出应如下所示： 为了进一步说明，所选单元格的 Excel 公式为： =IF(C3="买入";(I2*G2+D3*E3+F3)/G3;IF(G3>>0;I2;0))

我还尝试了 .groupby("ticker").apply() 使用shift()的函数，以便使用上面行中的先前值，但我失败了。

原文

I am trying to adjust my portfolio of stocks and trying to calculate the adjusted mean price (which is a form of weighted-average).

Here is sample data:


import pandas as pd 
import numpy as np

sample_dict = {'ticker': {1: 'ABCD',
  2: 'ABCD', 3: 'ABCD', 4: 'ABCD', 5: 'ABCD', 6: 'ABCD', 8: 'EFGH',
  9: 'EFGH', 10: 'EFGH', 11: 'EFGH', 12: 'EFGH', 13: 'EFGH'},
 'Date': {1: "2018, 1, 10",   2: "2018, 1, 20",
  3: "2018, 2, 7", 4: "2018, 4, 14",
  5: "2018, 5, 25", 6: "2018, 7, 4",
  8: "2018, 1, 10", 9: "2018, 1, 20",
  10: "2018, 2, 7", 11: "2018, 4, 14",
  12: "2018, 5, 25", 13: "2018, 7, 4"},
 'Sell_buy': {1: 'buy', 2: 'buy',  3: 'sell', 4: 'buy', 5: 'sell', 6: 'buy',
  8: 'buy', 9: 'buy', 10: 'buy', 11: 'buy', 12: 'sell', 13: 'sell'},
 'Qtd': {1: 100.0, 2: 300.0, 3: 200.0, 4: 500.0, 5: 600.0, 6: 500.0,
  8: 300.0, 9: 300.0, 10: 200.0, 11: 200.0, 12: 700.0, 13: 100.0},
 'Price': {1: 8.0, 2: 10.0, 3: 12.0, 4: 9.0, 5: 13.0, 6: 14.0,
  8: 8.0, 9: 10.0, 10: 12.0, 11: 9.0, 12: 13.0, 13: 14.0},
 'Costs': {1: 10.93, 2: 12.52, 3: 11.39, 4: 14.5, 5: 14.68, 6: 14.96,
  8: 10.93, 9: 12.52, 10: 11.39, 11: 14.5, 12: 14.68, 13: 14.96}}

sample_df = pd.DataFrame(sample_dict)
sample_df['Date']=pd.to_datetime(sample_df['Date'], dayfirst=True).dt.date

I was able to get the get an updated Adjusted quantity (based on buys and sells) without any issues:


#to calculate adjusted quantity. this works as expected
sample_df['Adj Qtd'] = sample_df.apply(lambda x: ((x.Sell_buy == "buy") - (x.Sell_buy == "sell")) * x['Qtd'], axis = 1)
sample_df['Adj Qtd'] = sample_df.groupby('ticker')['Adj Qtd'].cumsum()

However, I am not able to get the correct Adjusted Price.
The condition here is if I sell a stock, my Adjusted Price should not change and remain the same as the last adjusted price when the buy was made for that stock.

I've tried the following to achieve this goal:

#Adjust Price. Works good until I reach the row where a sell was made
sample_df['Adjusted Price'] = sample_df.apply(lambda x: ((x.Sell_buy == "buy") - (x.Sell_buy == "sell")) * (x["Price"] * x["Qtd"] + x["Costs"]), axis = 1)
sample_df['Adjusted Price'] = sample_df.groupby('ticker')['Adjusted Price'].cumsum().div(sample_df['Adj Qtd'])

I could partially correct the adjusted prices on sell rows with the following:

# When it's a "sell", adjusted price is the same from above
sample_df.loc[sample_df['Sell_buy'] == 'sell',['Adjusted Price']] = np.NaN
sample_df.fillna(method='ffill', inplace=True)

    ticker  Date    Sell_buy        Qtd Price   Costs   Adj Qtd Adjusted Price
1   ABCD    2018-10-01  buy       100.0 8.0     10.93   100.0   8.109300
2   ABCD    2018-01-20  buy       300.0 10.0    12.52   400.0   9.558625
3   ABCD    2018-07-02  sell      200.0 12.0    11.39   200.0   9.558625
4   ABCD    2018-04-14  buy       500.0 9.0     14.50   700.0   8.466514
5   ABCD    2018-05-25  sell      600.0 13.0    14.68   100.0   8.466514
6   ABCD    2018-04-07  buy       500.0 14.0    14.96   600.0   8.544733
8   EFGH    2018-10-01  buy       300.0 8.0     10.93   300.0   8.036433
9   EFGH    2018-01-20  buy       300.0 10.0    12.52   600.0   9.039083
10  EFGH    2018-07-02  buy       200.0 12.0    11.39   800.0   9.793550
11  EFGH    2018-04-14  buy       200.0 9.0     14.50   1000.0  9.649340
12  EFGH    2018-05-25  sell      700.0 13.0    14.68   300.0   9.649340
13  EFGH    2018-04-07  sell      100.0 14.0    14.96   200.0   9.649340

This would work perfectly if there is no "sell" between "buy"s (as it did for the stock EFGH in this example).
To be clear, when the transaction is a "sell", we must ignore adjusting the price and use the last adjusted price in the last buy type transaction for that particular stock.

I did all these calculus in excel and the output should be the following:
For further clarification, the excel formula for that selected cell was:
=IF(C3="buy";(I2*G2+D3*E3+F3)/G3;IF(G3<>0;I2;0))

Expected output base on excel sheet

I've also tried .groupby("ticker").apply() a function using shift() in order to use prior values from rows above, but I've failed.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（1）

×眷恋的温暖 2025-01-24 04:58:18

我想不出一个好的、简单的解决方案。问题是计算调整后的价格取决于调整后的价格的先前值，这妨碍了向量化或shift()的使用。

所以，这是一个丑陋的解决方案。 :)

第一步是使用 groupby，通过股票代码将其分隔。然后，它循环该组中的所有行，并计算价格的加权平均值以获取当前股票和之前的价格。然后，它将该列表添加为数据框中的一列。

def weighted_average(a, b, a_weight):
    """Take an average of a and b, with a weighted by a_weight"""
    assert 0 <= a_weight <= 1
    return a * a_weight + b * (1 - a_weight)

def get_adjusted_price_for_ticker(single_ticker_df):
    adjusted_price = 0
    current_shares = 0
    prices = []
    for _, row in single_ticker_df.iterrows():
        is_buy = row["Sell_buy"] == "buy"
        qtd = row["Qtd"]
        if is_buy:
            current_shares += qtd
            cost_per_share = (qtd * row["Price"] + row["Costs"]) / qtd
            proportion_of_new_shares = qtd / current_shares
            adjusted_price = weighted_average(cost_per_share, adjusted_price, proportion_of_new_shares)
        else:
            current_shares -= qtd
        prices.append(adjusted_price)

    single_ticker_df["Adjusted Price"] = prices
    return single_ticker_df

def get_adjusted_price(df):
    return df.groupby("ticker").apply(get_adjusted_price_for_ticker)

get_adjusted_price(sample_df)

输出：

   ticker        Date Sell_buy    Qtd  Price  Costs  Adjusted Price
1    ABCD  2018-10-01      buy  100.0    8.0  10.93        8.109300
2    ABCD  2018-01-20      buy  300.0   10.0  12.52        9.558625
3    ABCD  2018-07-02     sell  200.0   12.0  11.39        9.558625
4    ABCD  2018-04-14      buy  500.0    9.0  14.50        9.180321
5    ABCD  2018-05-25     sell  600.0   13.0  14.68        9.180321
6    ABCD  2018-04-07      buy  500.0   14.0  14.96       13.221654
8    EFGH  2018-10-01      buy  300.0    8.0  10.93        8.036433
9    EFGH  2018-01-20      buy  300.0   10.0  12.52        9.039083
10   EFGH  2018-07-02      buy  200.0   12.0  11.39        9.793550
11   EFGH  2018-04-14      buy  200.0    9.0  14.50        9.649340
12   EFGH  2018-05-25     sell  700.0   13.0  14.68        9.649340
13   EFGH  2018-04-07     sell  100.0   14.0  14.96        9.649340

I couldn't figure out a nice, simple solution to this. The problem is that calculating adjusted price depends on the previous value of adjusted price, which prevents the use of vectorization or shift().

So, here's the ugly solution. :)

First step is to use a groupby, to separate it by ticker symbol. Then, it loops over all the rows in that group, and calculates a weighted average of the price to get the current shares, and the previous price. Then, it adds that list as a column in the dataframe.

def weighted_average(a, b, a_weight):
    """Take an average of a and b, with a weighted by a_weight"""
    assert 0 <= a_weight <= 1
    return a * a_weight + b * (1 - a_weight)

def get_adjusted_price_for_ticker(single_ticker_df):
    adjusted_price = 0
    current_shares = 0
    prices = []
    for _, row in single_ticker_df.iterrows():
        is_buy = row["Sell_buy"] == "buy"
        qtd = row["Qtd"]
        if is_buy:
            current_shares += qtd
            cost_per_share = (qtd * row["Price"] + row["Costs"]) / qtd
            proportion_of_new_shares = qtd / current_shares
            adjusted_price = weighted_average(cost_per_share, adjusted_price, proportion_of_new_shares)
        else:
            current_shares -= qtd
        prices.append(adjusted_price)

    single_ticker_df["Adjusted Price"] = prices
    return single_ticker_df

def get_adjusted_price(df):
    return df.groupby("ticker").apply(get_adjusted_price_for_ticker)

get_adjusted_price(sample_df)

Output:

   ticker        Date Sell_buy    Qtd  Price  Costs  Adjusted Price
1    ABCD  2018-10-01      buy  100.0    8.0  10.93        8.109300
2    ABCD  2018-01-20      buy  300.0   10.0  12.52        9.558625
3    ABCD  2018-07-02     sell  200.0   12.0  11.39        9.558625
4    ABCD  2018-04-14      buy  500.0    9.0  14.50        9.180321
5    ABCD  2018-05-25     sell  600.0   13.0  14.68        9.180321
6    ABCD  2018-04-07      buy  500.0   14.0  14.96       13.221654
8    EFGH  2018-10-01      buy  300.0    8.0  10.93        8.036433
9    EFGH  2018-01-20      buy  300.0   10.0  12.52        9.039083
10   EFGH  2018-07-02      buy  200.0   12.0  11.39        9.793550
11   EFGH  2018-04-14      buy  200.0    9.0  14.50        9.649340
12   EFGH  2018-05-25     sell  700.0   13.0  14.68        9.649340
13   EFGH  2018-04-07     sell  100.0   14.0  14.96        9.649340
回复 原文
~没有更多了~

关于作者

疯狂的代价

暂无简介

文章
评论
27 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

alipaysp_snBf0MSZIv

文章 0 评论 0

梦断已成空

文章 0 评论 0

瞎闹

文章 0 评论 0

凯凯我们等你回来

文章 0 评论 0

寄意

文章 0 评论 0

似梦非梦

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文