make DATE macro support deltas like TODAY

[fmux]

Is your feature request related to a problem? Please describe. I'm trying to use the API of businessinsider.com to get JSON quotes for bond issues. A sample API output is like this:

https://markets.businessinsider.com/Ajax/Chart_GetChartData?instrumentType=Bond&tkData=1,1900639,18,814&from=20230601&to=20230630

The problem with this API is that the "to" date must be a valid date - you can't just use day 31 for every month, otherwise the API response will be empty (you can verify this by changing the final 0 to 1 in the above URL).

What I would therefore like to do is use a variable URL for getting monthly historical chart values as follows:

https://markets.businessinsider.com/Ajax/Chart_GetChartData?instrumentType=Bond&tkData=1,1900639,18,814&from={DATE:yyyyMM01:-P1M}&to={DATE:yyyyMM01}

However, the DATE macro does not support delta ranges as TODAY does.

Describe the solution you'd like

I would like for the above variable URL to generate one request per month, from the first of the previous to the first of the current month.

I'm not sure if having multiple DATE macros in one URL is supported at all - I know that if one macro of the form {DATE:yyyy-MM-01} is used, then PP will generate only one request for each month. One would have to take care that with a construct like the one above, only as many different requests are sent as there are different URLs generated.

Describe alternatives you've considered

I cannot currently think of any other way to use the businessinsider.com API for getting historical chart values (though I'm thankful for pointers).

[buchen]

Just for my understanding: Why do you need to request the data in a monthly fashion?

Can't you use one request to get the last six months, say something like this:

&from={TODAY:yyyy-MM-dd:-P6M}&to={TODAY}

The only caveat: you would have to open PP once every 6 months. If you do not do this, then it would result in gaps.

I could also add a LAST_PRICE_DATE macro which contains the date of the last historical Perice (and maybe a date 10 years ago if no prices exist.

Then the call would look like this:

&from={LAST_PRICE_DATE}&to={TODAY}

I can also look into extended the DATE macro. I am just wondering if there is an easier solution.

[fmux]

Many thanks for the quick response! I was using the URL for getting the historical prices, not for current ones. Right now my workaround is indeed specifying a fixed start date, which works reasonably well in this case since the bond I'm concerned about has been issued only a few weeks ago. However, in the long term I think it's kind of a bad solution to get all historical prices in one go every time the prices are updated (it would place undue load on their API if the issue date of the bond is longer than a few weeks or months ago).

It seems your comment was addressing mostly the case of getting current quotes. Please correct me if I'm wrong (it's not entirely clear to me yet how the relationship between historical and current price data works in PP). I was trying to use the historical quote JSON functionality as described e.g. in https://help.portfolio-performance.info/de/kursdaten_laden/#date.

Meanwhile I've found the discussion at https://forum.portfolio-performance.info/t/dynamische-kursdaten-urls/2929/21?page=2, which talks about possible issues one runs into when using DATE in connection with the PAGE macro. I'm guessing a similar caveat would apply in case of multiple DATE macros in one URL (one would have to take care not to create more requests than necessary and/or sensible). Of course I would be very happy if this is no issue here and the feature could be implemented by a simple extension of the existing code :smiley:

[chirlu]

I cannot currently think of any other way to use the businessinsider.com API for getting historical chart values (though I'm thankful for pointers).

Apparently 6-month periods are allowed, so perhaps whole years are, too? In that case, ...&from={DATE:yyyy0101}&to={DATE:yyyy1231} should work to get all historical prices, since December 31 is the last day for every year. PP will then generate and fetch one URL per year.

[chirlu]

So, I think a generic solution that would allow a lot of use cases with 100% backward compatibility might look like this:

• Extend DATE to support the same syntax as TODAY (as proposed above).
• Instead of a single time period to be added, allow a list of modifiers: {DATE:yyyy-MM-dd:mod1,mod2,mod3},
• where the modifiers could be time periods as before, but also other instructions similar to Java temporal adjusters, such as “end of month”, “next Tuesday”, “not later than today”.

Then, something like (with a provisional, overly verbose syntax) ...&from={DATE:yyyyMMdd:set-day-to=1}&to={DATE:yyyyMMdd:move-to-last-in-month} would solve the issue, generating ...&from=20231101&to=20231130 or ...&from=20230201&to=20230228 as required. Or possibly ...&from={DATE:yyyyMMdd:set-day-to=1}&to={DATE:yyyyMMdd:move-to-last-in-month,restrict-to-present,P-1D}, which would generate ...&from=20231101&to=20231116 today on 2023-11-17.

For a different website, {DATE:yyyy-MM-dd:move-to-next-weekday-if-not-already=Sunday} could generate all Sundays, i.e. 2023-11-19, 2023-11-12, 2023-11-05, 2023-10-29, etc.