Minimize Excel processing time (Formula IFERROR & INDEX used)

Hi Intelligent guys,

Hope you all are well and Good!

I'm new to this forum, Apologies if my English is not correct.

I'm exploring Excel for Stock Trading use and to be frank, don't know much about formula and VBA scripts.
I'm Trading lots of stocks daily, so I created Excel for trading records

What's in Excel
Main worksheet- Every Stock recorded there No formula use

Other worksheets- Created on basis of each stock for the performance of each stock. Reference from MAIN sheet formula use from 1st row to 200 rows on every stock worksheets

Formula:

Please Login or Register  to view this content.

Problem
Excel takes a lot of processing time when adding each line and when SAVE the file. Its 64bit version and Surface device
Showing always- Calculating(4 Threads)...12% bla bla



Requirements
1. Any formula or VBA/Macro scripts that fulfill my needs for research purposes.
2. Any Best Idea of Stock Trading records for Analysing and research.
3. Any automated system for Column K is Setup/Strategy used. Way to sort out which strategy works for me.
4. How to sort Worksheets according to its Size from High to Low(In other words Stock sheets which have more number of Rows stay first and less Rows Worksheet stay last). Is there any VBA script for it?


Notes
Coz Attachments limits of 1Mb I have added only 100 stock list. The actual File size is around 11Mb😱
Idea is to create around 150 stocks list in the MAIN worksheet


Any help is really appreciated
Thank you so much

Regards
Bhavesh

Hi Kvsrinivasamurthy,

Thanks for the valuable information. Just check out it really works great. Much faster typing and processing still file opening takes time but its okay.

Any suggestion for the below points?
1. Any automated system for Column K is Setup/Strategy used. Way to sort out which strategy works for me.

2. How to sort Worksheets according to their Size from High to Low(In other words Stock sheets which have more number of Rows stay first and fewer Rows Worksheet stay last). Is there any VBA script for it?

Thanks again really appreciated for Great work

Originally Posted by TMS

150 Stock sheets x 200 rows each x 16 columns is an awful lot of complex formulae to calculate.

Personally, I’d stick with the one Main worksheet but add some rows at the top where you can ask for selection criteria and use VBA to AutoFilter the data.

If it's a Structured Table, you could do the filtering and sorting manually.

Hi TMS,
Thanks for suggestion, Could you please make a small sample file so I can understand your strategy. Great if you take my excel file.

[BAny suggestion for the below points?[/B]
1. Any automated system for Column K is Setup/Strategy used. Way to sort out which strategy works for me.

2. How to sort Worksheets according to their Size from High to Low(In other words Stock sheets which have more number of Rows stay first and fewer Rows Worksheet stay last). Is there any VBA script for it?

Hi, kvsrinivasamurthy

MACRO Works awesome 👍

You saved my day, A million thanks to you 👏🙏