Is there a way to capture a cell value at a specified time in Numbers. For example if you have a stock portfolio and wish to capture month end values can this be done?

  • Like copy and then paste the value as opposed to the formula? – bmike Nov 1 '20 at 1:11

I am sorry that I don't know how to do what you are specifically asking for but I reckon it is not a trivial thing to do. In case you can't figure it out, I think either of the two below might be an alternative for you.

You can use the stock() function in Numbers to calculate the value of a stock portfolio with the latest closing prices. You can open this file within the first day of every month and it would give you the value of the portfolio at the closing prices of the last day of trading of the previous month assuming you will also be connected to the internet when you open the file.

You can then either record the value of the portfolio somewhere else such as another table in Numbers or save the table calculating the value of the portfolio to another file or sheet in Numbers as a whole by copying it first and then pasting it using Paste Formula Results menu option under Numbers' Edit menu at the top. The drawback of this option is that if you open the file calculating the value of the portfolio at a later date than the first of the month, it is likely to calculate the value of the portfolio for a date later than the end of the previous month.

Alternatively, you can use the stockh() function to calculate the value of a stock portfolio for any past date. You would of course need to know the exact number of shares per stock in the portfolio at the given past date for this to work.

  • Thanks. I have used the stockh function to recreate the portfolio value at each month end. Requires quite a bit of work but allows for scrutiny in the numbers. Thanks for help. – Kuldana1980 Jun 5 '20 at 7:29
  • No problem but I am a bit surprised that the eomonth() function described in another reply here has been the answer to your question. – Alper Jun 5 '20 at 8:23
  • I went for a combination of WORKDAY and EOMONTH to get me the last trading day of each month. Using that date I then used STOCKH to give the CLOSE price on securities on those dates. With cell A1 as January, in A2 type: =WORKDAY(EOMONTH(A1,0)+1,-1). Then in A3 I put =STOCKH("VOD.L","CLOSE",A2). All worked out well, allowing for monthly attribution of portfolio performance from each position in the portfolio. The WORKDAY and EOMONTH formula can be further refined to add Holidays (though the CLOSE price may be carried over). To be refined if the formula gives an ERROR. – Kuldana1980 Jun 6 '20 at 10:42
  • I am very happy that you have found a working solution to your problem but it appears that (as I expected) Eomonth() was only a small part of the solution to your problem. In such cases where an answer solves only part of your problem but does not answer it at least to a large extent, it might be better to mark that answer as helpful by upvoting it rather than marking it as the answer. – Alper Jun 6 '20 at 12:07

You can use the EOMONTH function to get the date of the last day of the month which is offset by input value. The string of functions EOMONTH(NOW(),-1) gives the date of the last day of the month for the month before now. You can use this new date to find the value you want.

For the end of this month, the day this answer was posted June 2020, you'd use: EOMONTH(NOW(),0)

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .