C_ARR_OHLCV on FORMULAS sheet updates on every workbook change

[scotchy33]

Hi Koen,

I am very impressed with your work, great job and thanks for sharing.

I don't know if it is intended, but it appears that:

C_ARR_OHLCV on FORMULAS sheet updates on every workbook change

I don't know if that was intended, but I don't think it should.

Scott

[scotchy33]

I think I figured it out.

Possibly these lines should be used:
Application.Calculation = xlManual

Application.Calculation = xlAutomatic

[krijnsent]

Hi @scotchy33
thanks for your feedback. The functions like C_ARR_OHLCV work, but are not ideal. With that I mean: they pull in the right information, but they do refresh with every recalculation of the workbook (which is probably set to automatic). To counter that a bit, I've built in a bit of caching, so the information gets pulled in from CryptoCompare only maximum every minute, but that's still the cause for a massive slowdown of sheets and/or crashing of workbooks.
If you're using the functions from a macro you could indeed add those lines in your code (as well as Application.ScreenUpdating = False ), but for the function (in sheet) that wouldn't work. The only real solution I found is moving the "get info online" to either PowerQuery or a RTD, but none of them are on my priority list now.
Cheers,
Koen

[scotchy33]

Hi @krijnsent,
Thank-you for the response. When going through some of the API information on Crypto websites, it says that you need an enterprise/pro (Paid version) API key for historical prices. However, your macro for historical prices works, even without an API key. Is there a limit on how often you can send requests to CryptoCompare and/or CoinMarketCap?

Thank-you,
Scott

[krijnsent]

https://min-api.cryptocompare.com/pricing -> 250k lifetime calls free. So say you want to use them up in the coming 5 years, that would be 250k/365/5=137 calls/day. If you know of any other good open APIs, do let me know, for now cryptocompare has been the only one I would recommend.

[scotchy33]

How do they track your calls if you do not enter an API key? I don't believe your macro is using one.
I can see them tracking an IP address for daily use, but to track an IP address until 250k calls have been used seems impractical.

[krijnsent]

No clue, not my problem :-). They hopefully have something on their side, from my side I only use their API. I expect some kind of error message when I go over my limit. I am using it moderately, guess max 10 calls/day.