Workday an Stockh functions working together

Question

Level 1

8 points

Workday an Stockh functions working together

Hi,

I'm setting up a table to track a stock portfolio and would like to measure the month to date performance. To do that, I first need to have both today's stock price (using the stock function) and the price that the stock was at the end of the previous month (using the stockh function). The stock function is straightforward as it always uses yesterday's date and it shows the stock's current price. However, for the stockh function you need to provide the date for the price that you need. If I want the formula give me the stock price for a specific I can input it manually but since I have a portfolio of stocks and would like this to be calculated every month, I would like this to be calculated automatically, so I though I could do the following:

For the holidays to be excluded, I have the following table:

The problem I'm having is that I need the workday formula to give me the stock price for the exact last trading session of the month (in this case, 28/05/21), so I have to use a 0 for work-days but when I do that it gives me 31/05/2021, which is on the holiday's table but it is not being excluded. I have tried many things but could not find a way around this.

Posted on Jun 22, 2021 2:20 AM

Reply

Answer 1

SGIII

Level 8

37,562 points

Jun 22, 2021 11:00 AM in response to jtlinsneto

jtlinsneto wrote:

the ultimate goal here is to find the performance of the portfolio but I don't see how I can do this collectively on a spreadsheet without having to calculate the performance for each and every stock throughout the current month and then add these performances together.

Generally the calculations are done at the portfolio level. You'll go crazy (and will end up with non-meaningful results) if you try to "add" performances together. Remember individual securities are constantly shifting their relative weights in the portfolio.

Here's a simplified example, which assumes you aren't putting in or taking money out.

The column at the right is an index that shows the growth (or shrinkage) of 100.

Columns B and C are entered. You just plug in the total portfolio market values.

The formula in D2 is =C2/B2−1

The formula in E2 is =E1*(1+D2)

In general non-trading days aren't a significant factor in tracking the monthly and cumulative performance of a portfolio.

If you are contributing and withdrawing money then there are standard ways to adjust the performance so that, say, a large contribution doesn't make it seem as if the portfolio performance is off the charts (or that a withdrawal makes it appear as if there was a large loss). If you need a way of estimating that, just post.

SG

Reply

Answer 2

SGIII

Level 8

37,562 points

Jun 22, 2021 7:54 AM in response to jtlinsneto

It's a little hard to follow what you are trying to do.

In measuring investment performance normally one looks at the starting and ending market value of the portfolio (not individual securities) and adjusts for any contributions or withdrawals from the portfolio).

If the end of the month falls on a non-trading day then the values from the previous trading day are generally used for the ending market value for that month.

It would help you could give more specifics on what you are trying to do.

SG

Reply

Answer 3

jtlinsneto Author

Level 1

8 points

Jun 22, 2021 10:28 AM in response to SGIII

You are right, the ultimate goal here is to find the performance of the portfolio but I don't see how I can do this collectively on a spreadsheet without having to calculate the performance for each and every stock throughout the current month and then add these performances together. Ok, let me try to get more specific to the problem I'm having and with that I think that I might be able to figure the rest of it out.

As I have previously shown, I have made a holidays table that I'll be using with the workday formula. On a separate table, I'm trying to get the workday formula to tell me what was the last workday before one of the holiday dates on the holidays table. I would usually use the second field (work-days) to tell the formula how many days prior to the last working day I want it to reference but if I do that for Monday, 31 May 2021, which was a holiday that was on the holidays table, I can't manage to have it show me the correct answer (Friday, 28 May 2021) regardless of which number I insert on the work-days field:

It seems that the formula treats the dates to be excluded (in this case, the holiday dates) in a different manner than a normal weekend. On the 1 and -1 examples above, it comes back with two working days whereas without the holidays table data it jumps a single workday at a time:

I didn't include the screenshots with 0 on the work-days field because it passes through the information and it basically just disregards the workday formula altogether.

Thanks!

Reply