Hi,
I have calculated the %age change in the attached workbook which applies to day, week and month (columns 2, 3 and 4) however this is not matching the default %age calculations provided by Optuma in columns 5, 6 and 7.
The idea is even if I am in a weekly worklist it should show for that day and month what the %age change was. Can you please let me know why the results are not identical to my calculations.
Thanks.
Karen
Hi Karen,
That wasn’t a workbook file so I was unable to open it. Note that if you change the timeframe of the watchlist to weekly it will load only weekly data (ie Monday’s open, Friday’s close, intraweek highs and lows). If doing % changes it will therefore calculate from the closest Friday close which is probably the cause of the discrepency.
Hi Darren,
I’m aware of the time frame toggle but I don’t believe that is the issue. Please see attached workbook. Thanks.
Optuma-sent-01.12.2021-3.owb (44.3 KB)
Thanks - it looks like you are using the percent change datafields from our Fundamental data - these will be a rolling monthly change and depending when you open the watchlist the database may not have been updated (the process that does the calculations and updates our servers are run overnight, so there will be a delay).
As such the values are a day behind, so in the example of FMG the 22.11% change is from the close on October 29th to the close on November 30th, as confirmed by the Time Price Measure tool between those dates:
These datafields were designed to be used in large watchlists (500+ symbols) to save system resources (see this article), but seeing as this is a small list use the CHANGE function set to the timeframe required, eg CHANGE(INT_TYPE=Month) instead (note that your formula calculates from the current month open and not the previous month’s close).
Hi Darren,
I can’t get the formula to work. The formula below is trying to work out the difference between yesterday’s close and today’s close as a percentage. Can you please let me know why it is not working.
(CLOSE(Day(PERIODAMOUNT=1), 0) - CLOSE(Day(PERIODAMOUNT=1), 1))/CLOSE(Day(PERIODAMOUNT=1), 1)
I would like to use a similar formula for the week and monthly formula. Please let me know what is wrong.
The formula CHANGE(INT_TYPE=Day) CHANGE(INT_TYPE=Week) CHANGE(INT_TYPE=Month) is not working when I toggle between day week and month on the worklist ie it is not showing the correct day, week and month calcs in the relevant time frame. Please let me know.
Thanks and kind regards,
Karen
Hi Karen,
You can’t use a lower timeframe in a script than the watchlist timeframe.
The daily change change can only be calculated on a daily timeframe as a watchlist using a higher timeframe (eg weekly, monthly) only loads weekly/monthly data. It’s the same as a weekly candle on a chart: you can’t see what the change was from yesterday, only the previous Friday.
Keep the watchlist set to daily and the CHANGE(), CHANGE(INT_TYPE=Week), and CHANGE(INT_TYPE=Month) will then calculate correctly.
Thanks Darren.
With the Change (M) formula the percentages end up changing for eg between the daily, weekly and monthly toggles. I would have thought that for eg the Change (M) should be the same no matter which toggle it is on. Is this working correctly?
I assume it is calculating from the close of last day in previous month to the last daily close of the current month? Please let me know.
Thanks,
Karen