search

krijnsent / crypto_vba

An Excel/VBA project to communicate with various cryptocurrency exchanges APIs
MIT License
155 stars 54 forks source link

C_ARR_OHLCV on FORMULAS sheet updates on every workbook change #71

Open scotchy33 opened 3 years ago

scotchy33 commented 3 years ago

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 commented 3 years ago

I think I figured it out.

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

Application.Calculation = xlAutomatic

krijnsent commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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.