I run 2 separate machines, both with decent spec.
One machine runs office 2010 and the other 2013.
Basically only make use of the 1st machine (2010) because I have 2 Excel add-ins that do not play well with 2013 upwards.
just recently both excel apps have been failing to completely load all my work books and are coming up with the message 'out of system resources '.
Have total of 38 workbooks containing 336 worksheets.
The only difference in my work is that I have started getting into pivot tables and charts.at the moment I have only created about 10 additional worksheets to contain these, but when completed will add another 200+ worksheets to the total.
I have installed 64-bit versions of both the office programs and at the moment everything seems to be holding up..
However I suspect that as I add additional worksheets it will only be a matter of time before problems start happening again.
Could anyone advise whether having a 3rd machine purely to run office/excel would do the trick or is it simply that excel can only handle a limited volume of workbooks/worksheets open at the same time?
having a bit of an excel nightmare
-
marksmeets302
- Posts: 527
- Joined: Thu Dec 10, 2009 4:37 pm
That's an impressive collection of sheets, but I'm wondering if excel is still the right tool for handling that?
Try opening the workbooks with the taskmanager open and look at the memory consumption. Maybe it's just a matter of adding more memory (which is cheap nowadays).
Without seeing the sheets it's hard to say where to look for improvements. Maybe data is calculated several times, memory not freed. In that case have a look if you can optimize that.
I remember we had some issues with large excel sheets back in the day. Some excel guru replaced a couple of common functions with others and this made a huge difference. Don't know what he did, but maybe with a little googling for excel efficiency tips you can improve the responsiveness.
A custom program is probably too far fetched, although if all sheets are basically a copy of each other that would help.
If the sheets are fully independent of each other then adding additional machines will help, until office 2013 is no longer supported...
Try opening the workbooks with the taskmanager open and look at the memory consumption. Maybe it's just a matter of adding more memory (which is cheap nowadays).
Without seeing the sheets it's hard to say where to look for improvements. Maybe data is calculated several times, memory not freed. In that case have a look if you can optimize that.
I remember we had some issues with large excel sheets back in the day. Some excel guru replaced a couple of common functions with others and this made a huge difference. Don't know what he did, but maybe with a little googling for excel efficiency tips you can improve the responsiveness.
A custom program is probably too far fetched, although if all sheets are basically a copy of each other that would help.
If the sheets are fully independent of each other then adding additional machines will help, until office 2013 is no longer supported...
thank you for the comments.
You may consider the number of sheets To be large, but the actual amount data within each sheet I would call minimal.
Standard 40 column layout for each sheet and maximum number of rows at present is 1500. Average is probably nearer 300 rows.
it would appear to be the number of sheets rather than the content that dictates excel memory usage.
In Excel 2013 the memory usage rises to 3500mb on 1st loading all the workbooks.which is where the out of resources problem probably arises. This then falls away to about 900mb.
I really do not have enough computing knowledge, let alone Excel capability.
So I'm going to go down the suck it and see route until it all grinds to a halt.then I will try the separate machine approach.
You may consider the number of sheets To be large, but the actual amount data within each sheet I would call minimal.
Standard 40 column layout for each sheet and maximum number of rows at present is 1500. Average is probably nearer 300 rows.
it would appear to be the number of sheets rather than the content that dictates excel memory usage.
In Excel 2013 the memory usage rises to 3500mb on 1st loading all the workbooks.which is where the out of resources problem probably arises. This then falls away to about 900mb.
I really do not have enough computing knowledge, let alone Excel capability.
So I'm going to go down the suck it and see route until it all grinds to a halt.then I will try the separate machine approach.
-
spreadbetting
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
I'd agree with marksmeets is there any reason why you're doing it all via excel? If you're data crunching surely something like MYSQL or even Access in MSOffice would be a better choice.
Even though each worksheets might not contain much data I think a separate instance of excel is opened for each one. Any reason why you can't simply merge the data on the sheets together?
Even though each worksheets might not contain much data I think a separate instance of excel is opened for each one. Any reason why you can't simply merge the data on the sheets together?
Mobile
Id agree with Mark/Spreadbetting.
Now you say that you dont have the skills to be able to do this. You dont always need them.
I outsource some of the stuff Im doing. You can find a whole industry out there that will do this for you. In the past Ive used Elance (now upwork) I think to find programmers etc.
Now, if you havent doen this before, what I will tell you is that they will take a project on for you and build you EXACTLY what you ask for. So you need to very specific about what you want.
On a project i did a few years ago, I wrote a very detailed 8 page specification. The programmer was able to go away and build it for me with only a few additional questions.
Only thing is these days, is that you will be changed by betfair for an API key if you havent one already (think it is £299), very unfair, but no way around this. (This wouldnt apply if you were able to use excel only)
Caveats: Dont give your username/password out, most developers familair with betfair will have their own for testing. (Same applies if you have your own API keys etc)
Regards
Peter
Id agree with Mark/Spreadbetting.
Now you say that you dont have the skills to be able to do this. You dont always need them.
I outsource some of the stuff Im doing. You can find a whole industry out there that will do this for you. In the past Ive used Elance (now upwork) I think to find programmers etc.
Now, if you havent doen this before, what I will tell you is that they will take a project on for you and build you EXACTLY what you ask for. So you need to very specific about what you want.
On a project i did a few years ago, I wrote a very detailed 8 page specification. The programmer was able to go away and build it for me with only a few additional questions.
Only thing is these days, is that you will be changed by betfair for an API key if you havent one already (think it is £299), very unfair, but no way around this. (This wouldnt apply if you were able to use excel only)
Caveats: Dont give your username/password out, most developers familair with betfair will have their own for testing. (Same applies if you have your own API keys etc)
Regards
Peter
-
marksmeets302
- Posts: 527
- Joined: Thu Dec 10, 2009 4:37 pm
Mobile,
I often ask stupid questions in the hope they lead to an overlooked solution: Are you sure you're using 64 bit versions of excel? If not, that will severely limit the amount of memory that can be used. It might also be the reason why your plugins don't work with the later versions... they're 32 bit then. Better start looking for a replacement for those plugins.
If not, check the amount of memory in your machines. If it's less than 8 GB, replace it with more. Otherwise I'd follow PeterLe's advise. There are very knowledgeable people willing to do this at what we would consider an absolute bargain.
I often ask stupid questions in the hope they lead to an overlooked solution: Are you sure you're using 64 bit versions of excel? If not, that will severely limit the amount of memory that can be used. It might also be the reason why your plugins don't work with the later versions... they're 32 bit then. Better start looking for a replacement for those plugins.
If not, check the amount of memory in your machines. If it's less than 8 GB, replace it with more. Otherwise I'd follow PeterLe's advise. There are very knowledgeable people willing to do this at what we would consider an absolute bargain.
Thank you for all your interest and suggestions.
I am familiar with the words access and SQL but have no idea of any of the complexities behind them.
I follow 14 major football leagues over the year.
One workbook one worksheet for each although this has now become 2 for each league as I am using a 2nd set of data.
My 3 letter code entered against each match where eligible. (17 variations).
Each 3 letter code has its own workbook with individual worksheets corresponding to each league.
Apologies if this appears confusing and more like something out of Monty Python as in 'and now for something completely different'.
Now I have the time, I am quite willing to work my way into either of the database examples given.
Which would be more appropriate to match the same kind of quick results that I can get using the slicers with a pivot table? I have attached a screenshot as example.
I am familiar with the words access and SQL but have no idea of any of the complexities behind them.
I follow 14 major football leagues over the year.
One workbook one worksheet for each although this has now become 2 for each league as I am using a 2nd set of data.
My 3 letter code entered against each match where eligible. (17 variations).
Each 3 letter code has its own workbook with individual worksheets corresponding to each league.
Apologies if this appears confusing and more like something out of Monty Python as in 'and now for something completely different'.
Now I have the time, I am quite willing to work my way into either of the database examples given.
Which would be more appropriate to match the same kind of quick results that I can get using the slicers with a pivot table? I have attached a screenshot as example.
You do not have the required permissions to view the files attached to this post.
making my 1st attempt with access.
Have found the Excel data import and an accompanying wizard.
However it's already telling me that I need to split my tables et cetera,, about which I have no real idea.
Don't wish to take up any more of your time on this.
But clearly I need to start from very basic.
As a final act could somebody recommend a simple starter book on this subject, or maybe online.
Once again, thank you very much for taking the time to try and steer me in the right direction.
Have found the Excel data import and an accompanying wizard.
However it's already telling me that I need to split my tables et cetera,, about which I have no real idea.
Don't wish to take up any more of your time on this.
But clearly I need to start from very basic.
As a final act could somebody recommend a simple starter book on this subject, or maybe online.
Once again, thank you very much for taking the time to try and steer me in the right direction.
-
marksmeets302
- Posts: 527
- Joined: Thu Dec 10, 2009 4:37 pm
I've never worked with access but see a lot of books about it when I'm at the library. Maybe that's a nice starting point?
Try YouTube, countless videos for beginners up to expert level
https://www.youtube.com/results?search_ ... +beginners
https://www.youtube.com/results?search_ ... +beginners
Mobile,
Some short term fixes that might help:
Search for improving excel memory and file performance - that will keep you busy/in the right direction for quick wins. little things like ...http://www.plumsolutions.com.au/article ... erformance
Sheets you dont need - hide them. Resources are minimized if they are invisible. (this might cause issues depending on your data references so try this on a backup)
Visual basic is the way to go for processing in excel. Depending on where you are at tech wise, you might want to start that journey (or outsource as someone suggested and then learn/build on from there)
Just seen your comment about access - from my quick read of your issues, you have x sheets for x leagues. This is the fundamental issue - you should aim to have one model for all leagues. Once you get your head around related data you will be away. Check out Power Pivot - this will do what you need although you will need to rebuild.
Assuming all your data (attributes) are the same across all leagues, you should have one sheet for each source. You can have millions of rows of data in 64bit excel
If you copy and paste data from a website into your sheets, you probably do want to contract a pro
With a good machine, excel can handle quite a lot... The application (model) just needs to be optimized when it is built is all.mobile wrote:Could anyone advise whether having a 3rd machine purely to run office/excel would do the trick or is it simply that excel can only handle a limited volume of workbooks/worksheets open at the same time?
Some short term fixes that might help:
Search for improving excel memory and file performance - that will keep you busy/in the right direction for quick wins. little things like ...http://www.plumsolutions.com.au/article ... erformance
Sheets you dont need - hide them. Resources are minimized if they are invisible. (this might cause issues depending on your data references so try this on a backup)
Visual basic is the way to go for processing in excel. Depending on where you are at tech wise, you might want to start that journey (or outsource as someone suggested and then learn/build on from there)
Just seen your comment about access - from my quick read of your issues, you have x sheets for x leagues. This is the fundamental issue - you should aim to have one model for all leagues. Once you get your head around related data you will be away. Check out Power Pivot - this will do what you need although you will need to rebuild.
Assuming all your data (attributes) are the same across all leagues, you should have one sheet for each source. You can have millions of rows of data in 64bit excel
If you copy and paste data from a website into your sheets, you probably do want to contract a pro
See here for some more info as to why you might have problems.
Used ranges, excessive resource hungry formulas etc.
http://chandoo.org/wp/2014/01/17/big-tr ... readsheet/
Used ranges, excessive resource hungry formulas etc.
http://chandoo.org/wp/2014/01/17/big-tr ... readsheet/