Solved

Scoring simplified

Posted on 2013-07-01
64
245 Views
Last Modified: 2013-07-10
Hi Experts,


Need Help On attched File.
EE.xlsm
0
Comment
Question by:itjockey
  • 36
  • 24
  • 3
  • +1
64 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289625
need password to protected file
need more information on requirement

or, "the answer is 42" - is that simple :)
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39289752
file is password protected.
gowflow
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39289854
0
 
LVL 8

Author Comment

by:itjockey
ID: 39289861
wait guys uploading new one .......
0
 
LVL 8

Author Comment

by:itjockey
ID: 39290446
Hi Experts,

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


Thanks.
Z-Score-V3.1.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39290543
Are we jocking here ??? What is the password ?
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39290562
sorry "vv"
0
 
LVL 8

Author Comment

by:itjockey
ID: 39290883
How do I update all formula at once it I had just copy past but other sheet..?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39290900
Copy|Paste Special|Formulas
0
 
LVL 8

Author Comment

by:itjockey
ID: 39290966
Wont work...
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39290971
Then please give some more detail about what you are trying to do.
0
 
LVL 8

Author Comment

by:itjockey
ID: 39291013
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.
0
 
LVL 8

Author Comment

by:itjockey
ID: 39291026
I need calculated data range I34:N500 from sheet "Rebuild" to respective sheet.
0
 
LVL 8

Author Comment

by:itjockey
ID: 39291139
there any macro for pls.....I had tried to press F2 - Entre....
0
 
LVL 8

Author Comment

by:itjockey
ID: 39291554
ok I am reframing the question. Hold on .....
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39292427

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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39293291
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39293429
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.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39294928
Will look at this tomoorow as now it is past midnite here.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39294943
Okie
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39296174
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39296437
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39297275
password protected workbook !
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39297476
Any Luck....!
Any Confusion Pls Let Me Know......
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39298027
What is the password ??? can't open it !!!
0
 
LVL 8

Author Comment

by:itjockey
ID: 39298062
"gowflow"
0
 
LVL 8

Author Comment

by:itjockey
ID: 39298095
Any confusion pls let me know, I am awake as US is closed tomorrow.So have to look Europe only.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39298228
I said the workbook you poted is password protectd what is the password I cannot open the workbook
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39298235
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39298265
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"
0
 
LVL 8

Author Comment

by:itjockey
ID: 39298309
Still you want me to close this question?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39299257
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
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 8

Author Comment

by:itjockey
ID: 39299348
Ok give me 20 min.
0
 
LVL 8

Author Comment

by:itjockey
ID: 39299371
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39299380
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39300682
ok now your talking.
Let me check this tomorrow as now past midnite !
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39303558
any luck
0
 
LVL 8

Author Comment

by:itjockey
ID: 39303790
Sir gowflow,

still have Confusion..?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39304997
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39305139
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39305142
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39305148
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39305152
ok fine clear. Let me work on it as it is Sunday will work tomorrow and revert.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39305154
ok


Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39305377
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39305592
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39305606
Sir do u know anything about technical analysis .....just asking?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39305701
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39306406
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39306433
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39306493
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39306543
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39306722
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39306733
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39306762
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39306779
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
0
 
LVL 8

Author Comment

by:itjockey
ID: 39306790
yes sir
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39306817
ok try this
gowflow
Z-Score-V3.2-VBA.xlsm
0
 
LVL 8

Author Closing Comment

by:itjockey
ID: 39306906
Awesome..
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39308039
your welcome. Pls feel free to post here any link to any question you may need help with.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39308084
Sure Sir.....
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39313465
Any idea on when you will post the link ? so just to know.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39313637
After 6 hours..

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39313884
ok
gowflow
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
Outlook Free & Paid Tools
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now