Hello I am a non technical user. I am using Excel 2016 Power Query to pull data from a web API.
The queries deliver 8 x JSON files and I have set the refresh rate to once per minute. The data is very basic financial market data (open,close, high, low etc) so I'm guessing the files are small.
The data is edited in Power Query and then presented in a s/sheet. As the data changes from time to time, the spreadsheet values update and old values overwritten. Some very basic excel calculations are performed on the data.
My PC has i7-7700 CPU @ 3.6GHz - 16gb RAM running Windows 10.
There are two issues:
1. There is a CPU spike every time an API data query happens. Often to 90% CPU usage on Resource Monitor. Why would this be I would have thought such a basic process would not use a lot of system resources.
2. Over time (usually 24-48 hours), Excel performance progressively declines and eventually crashes. If I look at Commit Memory in the Resource Monitor on initial activation of excel and the API pulls, I'll see commit memory of 200,000 Kb. After 24 hours this has increased to 1,500,000 Kb. Excel becomes unresponsive and I can visibly see that the API calls are sluggish.
Is there a way to "clear" memory without shutting down Excel as I link the spreadsheet to another application which can't have the link severed?
Perhaps it's possible to do the API calls in VBA...would this improve performance though I've no experience of writing VBA?
Thanks
The queries deliver 8 x JSON files and I have set the refresh rate to once per minute. The data is very basic financial market data (open,close, high, low etc) so I'm guessing the files are small.
The data is edited in Power Query and then presented in a s/sheet. As the data changes from time to time, the spreadsheet values update and old values overwritten. Some very basic excel calculations are performed on the data.
My PC has i7-7700 CPU @ 3.6GHz - 16gb RAM running Windows 10.
There are two issues:
1. There is a CPU spike every time an API data query happens. Often to 90% CPU usage on Resource Monitor. Why would this be I would have thought such a basic process would not use a lot of system resources.
2. Over time (usually 24-48 hours), Excel performance progressively declines and eventually crashes. If I look at Commit Memory in the Resource Monitor on initial activation of excel and the API pulls, I'll see commit memory of 200,000 Kb. After 24 hours this has increased to 1,500,000 Kb. Excel becomes unresponsive and I can visibly see that the API calls are sluggish.
Is there a way to "clear" memory without shutting down Excel as I link the spreadsheet to another application which can't have the link severed?
Perhaps it's possible to do the API calls in VBA...would this improve performance though I've no experience of writing VBA?
Thanks
