Scoring simplified

Hi Experts,


Need Help On attched File.
EE.xlsm
LVL 8
Naresh PatelFinancial AdviserAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
need password to protected file
need more information on requirement

or, "the answer is 42" - is that simple :)
gowflowCommented:
file is password protected.
gowflow
Martin LissOlder than dirtCommented:
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Naresh PatelFinancial AdviserAuthor Commented:
wait guys uploading new one .......
Naresh PatelFinancial AdviserAuthor Commented:
Hi Experts,

Need to get the update this formulas in "Rebuild" - "vv"


Thanks.
Z-Score-V3.1.xlsm
gowflowCommented:
Are we jocking here ??? What is the password ?
gowflow
Naresh PatelFinancial AdviserAuthor Commented:
sorry "vv"
Naresh PatelFinancial AdviserAuthor Commented:
How do I update all formula at once it I had just copy past but other sheet..?
Martin LissOlder than dirtCommented:
Copy|Paste Special|Formulas
Naresh PatelFinancial AdviserAuthor Commented:
Wont work...
Martin LissOlder than dirtCommented:
Then please give some more detail about what you are trying to do.
Naresh PatelFinancial AdviserAuthor Commented:
this small part of my process I want to create 53 Sheet like "LT" but may it take too large space I thought formula calculation is done on one sheet & copy past that data to relative sheet by running VBA Code & this data is updated every day i.e. each day one row data for each sheet.so that is why I use indirect function.

every day one line added to every 53 sheet (row 34 to row 500) so deleting oldest & updated newer. After that by Code copy past whole data to this "Rebuild" sheet & then new calculated data copy past to relative sheet one by one.
Naresh PatelFinancial AdviserAuthor Commented:
I need calculated data range I34:N500 from sheet "Rebuild" to respective sheet.
Naresh PatelFinancial AdviserAuthor Commented:
there any macro for pls.....I had tried to press F2 - Entre....
Naresh PatelFinancial AdviserAuthor Commented:
ok I am reframing the question. Hold on .....
gowflowCommented:

this small part of my process I want to create 53 Sheet like "LT"

or rather like Rebuild ??


every day one line added to every 53 sheet (row 34 to row 500)

added to LT ? at the end ?

You need to clarify what are the sheets that you have and what you need to aim and achieve so we can understand better. Pls try to explain it clearly and refrain from using words like copy/paste etc... just use plain simple English and leave us the burden to translate this into IT instructions.

gowflow
Naresh PatelFinancial AdviserAuthor Commented:
Mr. goflow,

See attached file it show whole process click on Button called "Process New Data" in sheet EOD. attached file is show process what I want to do?

i need modification in that or you say whole new kind of process.in attached - it preformed while clicking button - update data to there respective sheet (if date is already there then overwrite on that data) if in name list on sheet "EOD" there is any new name then create new sheet for that) if row data is less then 36 line then it don't perform any calculation as it required that much data.so only data is update for those calculation part remain blank.

Pass"bb"


Thanks
RedMod-DB-EOD-File-Generator.xlsm
Naresh PatelFinancial AdviserAuthor Commented:
Mr. goflow,

In attached sheet Problems -

if I go for say 10 day data then it taking too much time - it performed task of calculation 1 row at time so I need new way like after pressing button it just update data in respective sheet & for calculation there is new button. which process - copy data from data sheet & past to rebuild sheet  - done calculation & copy data of calculation & then past to their respective sheet.& delete from whole data from sheet rebuild. Then go for next sheet data.

But 1  drawback in this for calculation, I must have 500 row data, you have solution then pls guide me thanks. Attached sheet is just example I want mention process in Z Score V3.2.as there is new study added & old study have some modification in calculation.

if you understand then what am I trying to do then I will frame the steps.  just let me know.

Thanks.
gowflowCommented:
Will look at this tomoorow as now it is past midnite here.
gowflow
Naresh PatelFinancial AdviserAuthor Commented:
Okie
gowflowCommented:
ok what I gathered from what you explained is that you have a problem when you are updating more than 1 day say 10 days you feel it is taking too much time to process and you need to enhance this I am I right ???

If yes then I need to reproduce the problems you are having as looking at the sheet EOD I see only 1 day July 2 2013 to process so I guess this would not re-produce the problem you have. Could you post a sample EOD that have several days in the way they come naturally so I can see how to modify the code and get it more performing ?

furthermore I do not understand the following you need to explain it more clearly:


 it performed task of calculation 1 row at time so I need new way like after pressing button it just update data in respective sheet & for calculation there is new button. which process - copy data from data sheet & past to rebuild sheet  - done calculation & copy data of calculation & then past to their respective sheet.& delete from whole data from sheet rebuild. Then go for next sheet data.


gowflow
Naresh PatelFinancial AdviserAuthor Commented:
Sir goflow,

I don't want modification in above attached EOD workbook, I just gave you general idea what am I trying do. even if you modify for faster performance. here new file called "Z Score" which have new studies & same studies with some modification.

In EOD file whole process of populating data to respective sheet & its calculation is done by Module called "Rebuild". so I thought for my new study "Z Score", do it some different way. Instead of populating data & calculation done by Module, lets create separate sheets for calculations (in our case which is "Calculation" for Scoring & "Rebuild" for rest of calculation).

So for this question Task

In attached file sheet "Data" there is one button called Update - by clicking this button -

01. Populating Data

Each Name (Ticker) data populate to their respective sheet.

02. Over Past On Existing Data

If there is already have data for the date which is being populate in their respective name sheet then over past that data on that day data

03. Data Restriction

There always data available for  row 34 to row 500 i.e. if there is row 34 to row 500 data is already their then delete oldest data & add new data. their might be a case some time latest date for data is say 1 Jan 2013 & in Sheet "Data" is populating data for the date older than that date & that date data is missing in that respective sheet (So no over pasting Task) that time data to be added & delete the oldest one.

04. No Name Found

If there is name in "Data Sheet which to be populate is not found in all sheet then create new sheet with that name with all formatting & all as other name sheets have.
Thank You
Z-Score-V3.2.xlsm
gowflowCommented:
password protected workbook !
gowflow
Naresh PatelFinancial AdviserAuthor Commented:
Any Luck....!
Any Confusion Pls Let Me Know......
gowflowCommented:
What is the password ??? can't open it !!!
Naresh PatelFinancial AdviserAuthor Commented:
"gowflow"
Naresh PatelFinancial AdviserAuthor Commented:
Any confusion pls let me know, I am awake as US is closed tomorrow.So have to look Europe only.
gowflowCommented:
I said the workbook you poted is password protectd what is the password I cannot open the workbook
gowflow
gowflowCommented:
well don't ee what you want to do in this file pls be clear
what sheets need to be updated and how and from what sheet. it is all confusing your story ssorry cannot waste more time on this
gowflow
Naresh PatelFinancial AdviserAuthor Commented:
Password is "gowflow"
From data sheet to there respective sheet.match column A in data sheet to all sheet tab, there is only one match sheet is available in attached workbook which is "LT"
Naresh PatelFinancial AdviserAuthor Commented:
Still you want me to close this question?
gowflowCommented:
I did not say close it but clarify what you want without delay.

The workbook contain following sheets:
Data
LT
Rebuild
Calculation
Data For rerun

what is the sheet that correspond to EOD in the other workbook and what are the sheets that need to be updated. Pls post a workbook similar to the EOD workbook where it is clear what you want as this one has different format and is all different cannot just build a macro based on an idea to be similar to an other project.

give me clear steps in this workbook on what you need to achieve then I will be glad to assist. You may break these steps into smaller questions like I first proposed If you give me the whole issue clearly then if I find that some need to be broken down then I will let you know.

In these lines I can assist you.
gowflow
Naresh PatelFinancial AdviserAuthor Commented:
Ok give me 20 min.
Naresh PatelFinancial AdviserAuthor Commented:
Sir gowflow,

     All I need to do with this workbook only. There is no cross referencing or linking.
I had hide unwanted sheet for this question. Now there is sheets Data, ACC, AMBUJACEM, ASIANPAINT,LT.


I had manually copy past data to respective name sheet. In data sheet there is 51 names in column A. so now 4 is created manually. Rest is 47 which have to created by  Code & copy past its respective data. See attached. Password is same as provided.

this much you understood Sir?

Thanks
Z-Score-V3.2---Copy.xlsm
Naresh PatelFinancial AdviserAuthor Commented:
I am online now any query pls let me know. I try to respond you as soon as possible. As any comment you post it will take 5 to 10 min to reflect in my mail.

Thanks
gowflowCommented:
ok now your talking.
Let me check this tomorrow as now past midnite !
gowflow
Naresh PatelFinancial AdviserAuthor Commented:
any luck
Naresh PatelFinancial AdviserAuthor Commented:
Sir gowflow,

still have Confusion..?
gowflowCommented:
working on it.

By the way let me ask you a question:
I see from the past code that if a sheet does not exist you have a whole sub that create a new one based on code.

So my question is: Here also you may have sheets that does not exist can I assume that they will be similar to the 2 ones you created (without the line of data that you updated ?) like the first couple of rows has like a table but I see no formulas there is this normal and should be like this ??

gowflow
Naresh PatelFinancial AdviserAuthor Commented:
Sir gowflow,

Here also you may have sheets that does not exist can I assume that they will be similar to the 2 ones you created

YES

like the first couple of rows has like a table but I see no formulas there is this normal and should be like this ??

YES & also Z Score table which is start after row 500.

For Formula calculation i have some thing different in my mind & that will be my next question.

Thanks
gowflowCommented:
So what is the purpose of this question ?? what do you want me to achieve exactly in this question ?

What is Z Score Table ??? where do I find this sheet in the workbook you posted last there is not a sheet called Z core Table !!! pls be concise and clear I cannot keep asking and going round and round !!!!!


CORRECTION !!!
I just noted in every sheet at row 505 you have a scoring table I guess this is what you meant by that Z Scoring table right ?

So you need to tell me clearly what do you want to achieve in this question.
gowflow
Naresh PatelFinancial AdviserAuthor Commented:
By this question i want to just update data to there particular sheet(Name tab).See ID: 39296437.if name is not found which is in data sheet then create one with prescribed format & update data.(As like i had created 2 manually with one row data.)

Thanks
gowflowCommented:
ok fine clear. Let me work on it as it is Sunday will work tomorrow and revert.
gowflow
Naresh PatelFinancial AdviserAuthor Commented:
ok


Thanks
gowflowCommented:
got some spare time have 1question:

1) In your previous file you had data to be copied for a new worksheet from Row 23 to maximum 1498
Here the way I see it your data start at row 34 after the header in row 33 and down below you have the table starting at row 505 so say 500 is it enough for you to keep data from row 33 to 500 ??? as it is important at this stage of the design !!!! why you have a table below and why not say starting Col AA on Row1 till how much you wan ton the right and how much you want below then you would not be limited with data and could go as much as you want.

gowflow
Naresh PatelFinancial AdviserAuthor Commented:
It may increase size of workbook, and row 34 to row 500 approx contain at least 2 year data so it is enough for my study.
Any more question.....welcome.

Thanks
Naresh PatelFinancial AdviserAuthor Commented:
Sir do u know anything about technical analysis .....just asking?
gowflowCommented:
well nothing specific no. Not aware of any special issue or any special handling that is called technical analysis.

Pls see attached file and activate button Update in sheet Data and see if this is what you want.

Rgds/gowflow
Z-Score-V3.2-VBA.xlsm
Naresh PatelFinancial AdviserAuthor Commented:
Sir gowflow,

Pls see attached file and activate button Update in sheet Data and see if this is what you want.
Yes but need some modification.


No matter if data is updated for the date which is not in date range for that particular name sheet & date is not latest one. For Example if for "LT" data is already there for the date 08/7/2013 to 25/08/2011in their sheet, 8/07/2013 is very first data line (row 34),if update data for the date 06/07/2013 then 08/07/2013 will have to remain at position 1st (row 34) & below that data should be updated.

After each & every time data is updated all sheets should be in A to Z sorting. And only to those sheets which is not hidden & which is after "data" sheet.

The button which I had created on sheet "Data" it shift left after every click. cant we fix the position of button? I had repositioned  at row 1, Any thoughts...?

Thanks
Z-Score-V3.2-VBA.xlsm
Naresh PatelFinancial AdviserAuthor Commented:
Sir gowflow,

Ignore this point.

After each & every time data is updated all sheets should be in A to Z sorting. And only to those sheets which is not hidden & which is after "data" sheet.

Thanks
gowflowCommented:
ok this version prevent the button from moving. I saw nothing wrong in the code to update that data that already exist when it is found. I test it for sheet LT and changed the 7/1/2013 in sheet data for that record to 6/6/2013 and it updated that line in sheet LT.

gowflow
Z-Score-V3.2-VBA.xlsm
Naresh PatelFinancial AdviserAuthor Commented:
Sir gowflow,

Do one thing  from the same file which you attached - Copy sheet "LT" - A35:H35 which is data for the date 25/6/2013.

Past that data to sheet "Data", from date column to last - type "LT" in A2 (Sheet "Data") -change date 25/06/2013 to 26/06/13 manually in date column & then click on update button.

In my case 26/6/2013 appear in row 34 (which is very 1st row of data in sheet "LT" and after that I found data for the date 01/07/2013 which is latest then what we updated.

Thanks
gowflowCommented:
for sure it will put it there !!!!
I took your previous code and applied it. This means if you do this on your other workbbok it will also give you same behavior.

date 26/6/2013 does not exist when it does not exist it paste it on the first line ie line 34 and shift all the other rows down.

now if you need it to look for its position and insert it there it is a whole different routine that we will need to build.

I presumed that you wanted exactly the same thing that was done with your previous code. You tell me if this is the requirement then I will do it but you need to know that your other workbook works just like I explained and does not insert a line at its proper location.

gowflow
Naresh PatelFinancial AdviserAuthor Commented:
Previous version of workbook is updated to this latest version of workbook, so may be this thing is not done in previous version but in this version it is required?

Thanks
gowflowCommented:
Well let me check as I have an issue with searching dates with the Find method it always give weird results so let me comeback to you.
gowflow
gowflowCommented:
So let me summarize and here is an example so we don't make mistakes.

33     Date
34     7/1/2013
35     6/25/2013
36     6/24/2013
...

499   8/16/2011
500   8/12/2011

So say we have date 6/26/2013 it should then go on row 35 and all the rest shifted down till row 500 and you should have something like this:

33     Date
34     7/1/2013
35     6/26/2013
36     6/25/2013
37     6/24/2013
...

499   8/17/2011
500   8/16/2011

and with this operation the previous line
500   8/12/2011
will be deleted.

If the row is found then it should replace its current data with the data it found in sheet Data and if it is not found it should 'Insert it' at its proper place so the dates are sorted correct ????


gowflow
Naresh PatelFinancial AdviserAuthor Commented:
yes sir
gowflowCommented:
ok try this
gowflow
Z-Score-V3.2-VBA.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Naresh PatelFinancial AdviserAuthor Commented:
Awesome..
gowflowCommented:
your welcome. Pls feel free to post here any link to any question you may need help with.
gowflow
Naresh PatelFinancial AdviserAuthor Commented:
Sure Sir.....
gowflowCommented:
Any idea on when you will post the link ? so just to know.
gowflow
Naresh PatelFinancial AdviserAuthor Commented:
After 6 hours..

Thanks
gowflowCommented:
ok
gowflow
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.