C_ARR_OHLCV on FORMULAS sheet updates on every workbook change
Opened this issue · 6 comments
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
I think I figured it out.
Possibly these lines should be used:
Application.Calculation = xlManual
Application.Calculation = xlAutomatic
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
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
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.
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.
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.