[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 868
  • Last Modified:

Formula Merge RSI

Hi Experts,

Need to merge 3 column formula in one cell...


pls see attached file.

Thank You
Formula-Merging.xlsx
0
itjockey79
Asked:
itjockey79
  • 47
  • 32
1 Solution
 
redmondbCommented:
Hi, itjockey79.

If I understand you correctly, the resulting formulas are going to be hugely complicated, especially as many of the formulas to be merged refer to equivalent values on the next line. Whoever in future has to update (or even understand!) the formulas will have a very difficult job.

Instead, what about a macro which is run whenever a new day's data is added? It would process each sheet's columns A:F by temporarily creating the current formula columns, convert the formulas to values and then delete the unwanted columns. You'd end up with exactly what you want (except that the "Blue" columns would be values rather than formulas) and it would be much easier to maintain.

Regards,
Brian.
0
 
itjockey79Author Commented:
really it is possible ? will provide me sample ?


Thank You
0
 
redmondbCommented:
itjockey79,

I'm not sure what you mean by a sample, but here's what the sheets would look like after the macro ran.

Regards,
Brian.Formula-Merging-Mock-Up.xlsx
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
redmondbCommented:
itjockey79,

A question, please.  As you pointed out, the "end" of columns I:K are different to the cells above them. Is there a rule for identifying these special cells? (e.g. They always start on 03/10/2011 - I wish!)

Thanks,
Brian.
0
 
itjockey79Author Commented:
good one this is what i needed actully i have 50 or more symbol.it makes my file too loaded.i dont think in this way thanks for suggesting......
0
 
redmondbCommented:
itjockey79,

So you do want the specials to start in 03/10/2011?!! If I can't find that date, do I just use the standard formula on all the cells?

How is the mock-up?

Regards,
Brian.
0
 
itjockey79Author Commented:
no start date will differ but sheet restrict data up to 1500 row, so there is always last entry in row 1500 for date open high low close volume
0
 
redmondbCommented:
itjockey79,

Thanks, but I've two important questions....
(1) Is the mock-up correct?

(2) Am I correct that....
     Columns A:F end on row 1500.
     Column G ends on row 1475.
     Columns H:I end o n row 1468.
If not, please provide a correct sample sheet.

Thanks,
Brian.
0
 
itjockey79Author Commented:
ok see attached file this the one sheet of bunch of 51, rest one also have same fields, as you solved data generator the data transfer to this sheets & done calculations,i required RSI,MACD,MACD Diff,Signal ZigZag      Date      Cross      Time      Price      Action      CD
this is the column which required,rest is not required but it is need to calculaion,so tell me what you can do to thing working better...


Thank You
Mock-Up-V1.xlsx
0
 
redmondbCommented:
itjockey79,

As well as the questions in my previous post...
(3) Should the RSI sheet also have special cells at the end? (I see that it's processing cells beyond the end which is causing errors.)

My code now handles the MACD sheet, exactly replicating the current figures in the MACD, Diff and Signal columns. However, there's no point in my doing the RSI sheet s until the three questions have been answered.

Thanks,
Brian.
0
 
redmondbCommented:
itjockey79,

Apologies, our last two posts were almost simultaneous and I didn't notice your post until now.

We've got a serious problem as your mock-up is fundamentally different to everything that has gone before. I'll try to stay with the question, but I need all of the following to be responded to. Please use my numbers in your answers so that it's clear which answer goes with which query.

I entered these questions as they occurred to me, but looking over them,  the most important question is (4), especially (4D). Please concentrate on that one as, without a clean answer to it, all the rest are irrelevant to me.

(1) What is the formula for the RSI column?

(2) Please send a mock-up of the output (i.e. with the "work" columns removed.

(3) Maximum row no. is 1499 (not 1500), correct? (Edit: In different files, your data starts on different rows - is it 22 or 23?)

(4) Despite the problems caused by all of your changes, the biggest issue is the "Special cells" (columns G:K).
     (A) Why is there not similar processing for columns N:Z - all of those columns refer to cells below 1499?
     (B) Is the last cell for each columns correct?
          G is row 1488.
          H is row 1474.
          I is row 1474.
          J is row 1474.
          K is row 1466.
     (C) Do your averages really vary as wildly as your file shows?
          G1488 averages 12 items.
          H1474 averages 26 items (including all of those included in G1488!).
          I1474 is not an average.
          J1474 is not an average.
          K1466 includes 9 items.
      (D) It might just be possible to deal with all of the above, if every file ended on row 1499, but what are the rules when the last row is 1498? 1497? 1465? 23?

Thanks,
Brian.

Edit: Just to show you what's possible, if you press the Blue "Rebuild" button in the attached, it clears columns G:Q and rebuilds them. The result is  identical to the original file. OK, that's the good news, the bad news is that it's hard-coded to expect the data to start in row 23 and the last row to be 1499.Mock-Up-V3.xlsm
0
 
itjockey79Author Commented:
Sorry Sir i am not clearly asking & messed up pls give me 1 hour i will give you answer to you 1 to 4 & as EOD final is done today so total data row is up to  1500 in EOD Final, i had attaching that file also.....
0
 
redmondbCommented:
itjockey79,

I was very worried about the problem mentioned in (4D) above, but I think I have a solution.
 - All of your company sheets are currently "full", i.e. 1500 rows (actually 1778).
 - The "full" sheets are not a problem as the the formulas in their "special cells"  don't change.
 - The problem is new companies - until they reach 1500.

So, I suggest that, for a new company, we fill the sheet - as soon as they are created they will be given formulas all the way down to row 1500. The formulas would need a slight change so that if the Date (column A) is blank then they return 0, but that's not a big deal.

The cost of this is that the file will become a little bigger than it needs to be. However, we are dropping hundreds ofthousands of formulas so I think that's probably a good exchange!

So, don't worry about (4D) - however I still need answers to all of the other items.

Regards,
Brian.
0
 
redmondbCommented:
itjockey79,

I've made the changes I mentioned in my previous post. I have also merged this question's file and the one from the other question.

So, in "EOD" click on the "Process New Data" button. Then click on the "Rebuild All" button. It doesn't matter now how many entries a company has - they're, hopefully, correctly handled. (Well, they look OK, but obviously they will need to be carefully tested - once all the all other issues are sorted out!)

Finally, I still need the answers to items (1) to (4C)!

Thanks,
Brian.EOD-All-V1.xlsm
0
 
itjockey79Author Commented:
(1) What is the formula for the RSI column?
Ans. in this new file i have calculated RSI formula.

(2) Please send a mock-up of the output (i.e. with the "work" columns removed.
Ans.see attached file (Layout).xlxs i.e front view

(3) Maximum row no. is 1499 (not 1500), correct? (Edit: In different files, your data starts on different rows - is it 22 or 23?)
Ans.'-- restrict data insertion to A23:F1500
                dst.Range("A23:F1499").Copy dst.Range("A24:F1500")
this is the code line,data start line is A23.

(4) Despite the problems caused by all of your changes, the biggest issue is the "Special cells" (columns G:K).
Ans.i had just do some thing else & it may remain by mistake,so in new attached file there is no special cell, if there then just change it.
 
(A) Why is there not similar processing for columns N:Z - all of those columns refer to cells below 1499?
Ans. i dont get it what you are asking ,there is (new attached file )column R LRS, S ZigZag & T Date ,that is set of one study which take it to account column E (Close) by Column R (LRS), any positive to negative or vice versa is noted down by S (ZigZag) column & find specific price & for that price column T (date) of that price noted.so in cell S23 i had given formula & draged down till 1499 row that is why it is happen but it required if there is neg to positive change find there then calculation have to search till range 1500.it is just drag down formula there is n study behind referring below 1500.

column U to X is one part of Cross study same it just drag down formula cross column is start point of study & it take in to account MACD diff column i.e change + to - & - to +,so MACD diff is available up till J1466 (assuming data is coded up till 1499 after that restricted) so below that still require becoze it search for previous 5 data point so j1471 will be range search below that formula not require.


(B) Is the last cell for each columns correct?
          G is row 1488. yes
          H is row 1474.yes
          I is row 1474.yes
          J is row 1474.  1466
          K is row 1466.

(C) Do your averages really vary as wildly as your file shows?
          G1488 averages 12 items.
          H1474 averages 26 items (including all of those included in G1488!).ye s it like that only it is EMA but if you see real value then you will amaze how much fluctuation is there in actual price, to soften this variation have to average it.
          I1474 is not an average.no it is not, it result of fast EMA minus Slow EMA which is in our case 12EMA- 26 EMA
          J1474 is not an average.J1466 would be the last for that entry sorry for mistake,J1466 is results of MACD - macd signal i.e column i- k
          K1466 includes 9 items.that is 9EMA of MACD  that is why.

      (D) It might just be possible to deal with all of the above, if every file ended on row 1499, but what are the rules when the last row is 1498? 1497? 1465? 23?

yes this problem arise i  dont know how to deal with, 1 instance i had choose most tradable symbol so that is why i dint face this problem but after that i had face this problem in some symbols.....pls you suggest...



i am Extremely sorry for changing again & again file, but previously i spilt my task in to many part & search for answer,if i dont get it then come to EE,if i ask whole bunch of question no one interested.you are the only guy who told me to merge questions...quite impressive...

As i had change some calculation which affects the code of EOD sheet, pls check it,

pls see NIFTY Future sheet for calculation, i will rectify if there is difference between others

Thank You
EOD-Final-Red-Tag---line-1500---.xlsm
0
 
itjockey79Author Commented:
Sir if it is helpfull it hte code for MACD but it only produce MACD Diff & calculation is done from top to bottom not bottom to top


Sub ETmacd()
Dim EMAslow As Double, EMAfAst As Double, ws As Worksheet, LR As Integer
Dim eMaF() As Double, eMaS() As Double, EMAdif(), emaPer() As Double, MacDper As Double, coUnt As Integer
Dim DataRange As Range
Dim ExPSlowWeight As Double
Dim ExPFastWeight As Double
Dim PerWeight As Double

EMAslow = 26
EMAfAst = 12
MacDper = 9
ExPSlowWeight = 2 / (EMAslow + 1)
PerWeight = 2 / (MacDper + 1)
ExPFastWeight = 2 / (EMAfAst + 1)
Set ws = ThisWorkbook.Worksheets("VBA")
    With ws
    LR = .Cells(Rows.coUnt, "A").End(xlUp).Row
        For Each DataRange In ws.Range(.Cells(2, "A"), .Cells(LR, "A"))
        coUnt = DataRange.Row + 1
        'fill the eMA slow Array
        ReDim Preserve eMaS(1 To coUnt)
                If coUnt = EMAslow + 1 Then
                    'get the first value which is the Simple Moving average
                     eMaS(coUnt) = Application.Average(ws.Range(.Cells(2, "E"), .Cells(coUnt, "E")))
                ElseIf coUnt > EMAslow Then
                    eMaS(coUnt) = (.Cells(coUnt, "E") * ExPSlowWeight) + (eMaS(coUnt - 1) * (1 - ExPSlowWeight))
                End If
        Next DataRange
'fast
        For Each DataRange In ws.Range(.Cells(2, "A"), .Cells(LR, "A"))
        coUnt = DataRange.Row + 1
        'fill the eMA slow Array
        ReDim Preserve eMaF(1 To coUnt)
                If coUnt = EMAfAst + 1 Then
                    'get the first value which is the Simple Moving average
                     eMaF(coUnt) = Application.Average(ws.Range(.Cells(2, "E"), .Cells(coUnt, "E")))
                ElseIf coUnt > EMAfAst Then
                    eMaF(coUnt) = (.Cells(coUnt, "E") * ExPFastWeight) + (eMaF(coUnt - 1) * (1 - ExPFastWeight))
                End If
        Next DataRange
        ReDim Preserve EMAdif(EMAslow To UBound(eMaF))
            For coUnt = EMAslow + 1 To UBound(eMaF)
                EMAdif(coUnt) = eMaF(coUnt) - eMaS(coUnt)
            Next coUnt
'MacD Period
        Dim x As Integer, y As Integer, z As Integer, Avee As Double
        y = EMAslow + MacDper - 1
            For x = y To UBound(EMAdif) - 2
                'get the SMA for first value
                If x = y Then
                        For z = EMAslow + 1 To EMAslow + MacDper  '(EMAslow + MacDper - 1)
                        Ave = Ave + EMAdif(z)
                        Next z
                        ReDim emaPer(x To LR)
                        emaPer(x) = Ave / MacDper
                ElseIf x > y Then
                emaPer(x) = (EMAdif(x + 1) * PerWeight) + (emaPer(x - 1) * (1 - PerWeight))
                End If
            Next x
  x = Empty: y = Empty: z = Empty
  x = LBound(emaPer)
  y = UBound(emaPer)
  For z = x To y - 1
  .Cells(z + 1, "J") = EMAdif(z + 1) - emaPer(z)
  Next z
End With
End Sub
0
 
itjockey79Author Commented:
before you go further i have to tell you one thing that Zig Zag study is not full prof, for that i had raised one more question called Zig Zag RT, if you find any clue pls let me know in which way i go ahead or search for .......


Thank You
0
 
redmondbCommented:
itjockey79.

Edit: I just saw your Zig-Zag post - don't worry about that for now.

Every time you change things it not only means more work but, worse, it also means that a lot of the work already done is wasted. So I want to be sure that we both have a clear view of where you are and where you want to be.

Therefore, before I can do any more with this question, I need a couple of things from you, please...
 - A sample file of your data exactly as it is now. There is no need for 50 company sheets - one or two of them plus EOD, plus BB are enough.
 - A sample file of your data exactly as you want it to be in the future. (Again, one or two company files are enough.)

Thanks,
Brian.
0
 
itjockey79Author Commented:
yes sir that is final file above 20 row is purposely leave blank for further add study in future.right now this is the final no more addition.BB sheet is source of data but it may differ in future but purpose is same,it will remain data sheet only.
0
 
redmondbCommented:
itjockey79,

No, the file above is not what I have asked for.

For example, each of the company sheets ends on row 1499 not 1500. So, I expect that cell G1489 should be "=AVERAGE(E1489:E1500)" instead of blank.

But perhaps I'm wrong - that's why I must have correct data! Please provide the two files I have asked for.

Regards,
Brian.

I must have
0
 
itjockey79Author Commented:
pls see attached file data ends at row 1499, so not G1489 should be "=AVERAGE(e1489:e1500)"

right one is cell G1488 "=AVERAGE(e1488:e1499).
EOD-SAMPLE-AS-REQUESTED.xlsm
0
 
itjockey79Author Commented:
Sir, i am worried about sheets which have not enough data i.e less then 1499 row, as in above sample sheet i had included BAJAJ AUTO sheet which have error, what to do with that if that thing not solved & we go ahead it might lost of work.

i have one solution in my mind instead of our data from A to Z newer to older we can arrange older to newer so formula line will fix.& we restrict data up to 1499 if new entry which is 1500 then remove older one (row 23) & add to row 1499 new data ...pls suggest .....?
0
 
itjockey79Author Commented:
after this just want to add one study but which called ATR,just give me 2 hours i am sending you, iam middle of calculation.
0
 
redmondbCommented:
itjockey79,

Below I have included 5 quotes from 4 of your posts in which you refer to row 1500. You now say that the last row is 1499. This may seem like a trivial matter to you, but this is of the utmost importance - it must be sorted out before we can continue.

(1) Are you absolutely, positively, bet-your-life certain that the last row is 1499?

(2) If the answer to (1) is "Yes!", where did 1500 come from?

The quotes....

no start date will differ but sheet restrict data up to 1500 row, so there is always last entry in row 1500 for date open high low close volume

Sorry Sir i am not clearly asking & messed up pls give me 1 hour i will give you answer to you 1 to 4 & as EOD final is done today so total data row is up to  1500 in EOD Final, i had attaching that file also.....

Ans.'-- restrict data insertion to A23:F1500
                dst.Range("A23:F1499").Copy dst.Range("A24:F1500")
this is the code line,data start line is A23.


Ans. i dont get it what you are asking ,there is (new attached file )column R LRS, S ZigZag & T Date ,that is set of one study which take it to account column E (Close) by Column R (LRS), any positive to negative or vice versa is noted down by S (ZigZag) column & find specific price & for that price column T (date) of that price noted.so in cell S23 i had given formula & draged down till 1499 row that is why it is happen but it required if there is neg to positive change find there then calculation have to search till range 1500.it is just drag down formula there is n study behind referring below 1500.

EOD-Final-Red-Tag---line-1500---.xlsm

Regards,
Brian.
0
 
itjockey79Author Commented:
ok done with it, final look of file is look sheet TATA STEEL all that column require so it should be appear in all sheets,ATR study included.


Thank You very much.
EOD-SAMPLE-AS-REQUESTED.xlsm
0
 
itjockey79Author Commented:
still confused go for Z to A or this will be fine....?
0
 
redmondbCommented:
itjockey79,

Please answer the two questions in this post.

(Sorry, I don't understand this - "still confused go for Z to A or this will be fine....?".)

Regards,
Brian.
0
 
itjockey79Author Commented:
It mean  sort as bajak auto sheet don't have enough  data i.e 1499  so thedw is errpe in sheet so i thought instead of calculation done bottom to top why don't we start our calculation top to bottom so then there Will be no issue of less data if there is only 50  data point data still we get result as cal elation done from.top.to bottom but for that our data arrangement would be row 23 Will oldest data point omit sheet and raw 1499 is newest dAta point in sheet
0
 
itjockey79Author Commented:
Apology  for spelling  as i am typing from my cell which i am not used too
0
 
itjockey79Author Commented:
Extremely sorry sir for confusion now it is up to you, do whatever row you want 1500 or 1499.if code is for 1500 then use 1500 (Ctrl+o) but you want to start with 1499 then pls change to EOD sheet code to 1499 restriction,

By asking A to Z or Z to A : our calculation on symbol sheet goes bottom to top, & our formulas is hard coded to 1499 or 1500 row so if any symbol have less then (1499 or 1500) data then error arises so i thought despite of calculating bottom to top why don’t we calculate top to bottom so in that case if only 50 data point available even then we get results instead of error in current process. but to do this way we have to arrange our data (date open high low close ) which we generated from EOD sort oldest to newest, row 23 have oldest data in whole sheet & row 1500 have newest data in whole sheet.
0
 
itjockey79Author Commented:
sir it is up to you about last row to take it in to consideration, as i am not VBA programmer,(i am  learning) so i don’t know what code does had made this file from other expert & same with you, so there is confusion about last line of the row ,
0
 
redmondbCommented:
itjockey79,

(1) I don't care whether it's 1499 or 1500! What I do care about is that the formulas for the "special cells" are correct for the number you finally pick.
If you have now settled on 1499, please check the "special cells" (addresses below) and confirm that their formulas (or absence of formulas) are correct in the second EOD-SAMPLE-AS-REQUESTED.xlsm file.
The addresses are...
G1489:G1498
H1474
H1475:I1498
J1467:J1498
K1466:K1498
R1491:R1498
G1499:T1499
U1466:AE1499.

(2) I told you and then showed you that I had a solution for the companies which had less than 1499/1500 entries. If there's anything wrong with my solution then tell me - otherwise that problem is solved, so please leave it alone.

(3) I am not even thinking about the other points you raise until 1499/1500 is sorted!

Regards,
Brian.
0
 
itjockey79Author Commented:
itjockey79,

I've made the changes I mentioned in my previous post. I have also merged this question's file and the one from the other question.

So, in "EOD" click on the "Process New Data" button. Then click on the "Rebuild All" button. It doesn't matter now how many entries a company has - they're, hopefully, correctly handled. (Well, they look OK, but obviously they will need to be carefully tested - once all the all other issues are sorted out!)

Finally, I still need the answers to items (1) to (4C)!



Thanks,
Brian.
9.4 MB EOD_ALL_V1 EOD-All-V1.xlsm



yes this is the solution you are right sir,but i just added few more study will you pls included in your rebuild button.....? pls




If you have now settled on 1499, please check the "special cells" (addresses below) and confirm that their formulas (or absence of formulas) are correct in the second EOD-SAMPLE-AS-REQUESTED.xlsm file.
The addresses are...
G1489:G1498
H1474
H1475:I1498
J1467:J1498
K1466:K1498
R1491:R1498
G1499:T1499
U1466:AE1499.



it is absence of formula cell......
0
 
redmondbCommented:
itjockey79,

it is absence of formula cell......
Apologies, my mistake. I had prepared the list from an earlier version of the file and missed that you had changed some specials. The updated list for NIFTY FUTURE from your second EOD-SAMPLE-AS-REQUESTED.xlsm is below. Is it correct?
Formulas
G1488
H1474
K1466
Absence of Formulas
G1489:G1499
H1475:H1499
I1475:I1499
J1467:K1499
L1499:Q1499
S1499:U1499
V1493:V1499
W1491:W1499
X1499:Y1499
Z1466:AA1499
AB1466:AB1497
AB1499
AC1466:AJ1499
Unusual Formulas
AB1498 [It has a formula, but the 32 cells above it have none.
R1499    [The only formula on Row 1499.]

In summary, 3 questions, please...
 - Is the second EOD-SAMPLE-AS-REQUESTED.xlsm the file with the correct formulas?
 - In that file, is NIFTY FUTURE the sheet with the correct formulas?
 - Are the "Special cells" listed above correct? (Edit: Be careful - these are different to the cells in TATA STEEL!)

Thanks,
Brian.
0
 
itjockey79Author Commented:
Unusual Formulas
AB1498 [It has a formula, but the 32 cells above it have none.
R1499    [The only formula on Row 1499.]

Ans AB1498 by mistake it remain, that is blank cell i.e no formula
R1499 is take data for current row so it has taken but by pin pointing you i think it is not require for any calculation so it is also be a blank no problem.

(you have a Eagle eye  :)   )

nifty future sheet is totally correct one (only just delete formula in cell R1499 & AB 1498).

after you suggested there is requited only 2 to 3 sheet for sample, so  i had made changes only in nifty future sheet so that is final & all formula is correct on that sheet, except listed above.

so calculation part sheet nifty future is correct sheet but after your macro run (rebuilt button) i want appearance like in tata steel sheet i.e i had hide column in that sheet, which is not require & display only that which is required.


Thank You Very Much
0
 
redmondbCommented:
itjockey79,

Thank you for your patience! The good news is that I'm happy with everything in your last post and I think that I now have an accurate view of what you want. I'll get on to it this evening and should have something for you tomorrow.

Regards,
Brian
0
 
redmondbCommented:
Itjockey79,

OK, lots here.

(1) The attached is just testing Rebuild, so I've dropped "process_new_data()" and most of the sheets.

(2) The two remaining company sheets are “Test” (a copy of your new “NIFTY FUTURE”) and “TEST (SHORT)” (the first 16 entries of "TEST").

(3) I changed the formula in column G so that the same formula can be used for the entire column.

(4) You are currently using Merged cells in columns C:F. That's OK, but you will cause issues if you use them elsewhere. If that's a problem then please tell me!

(5) On the “TEST” sheet, run the macro by pressing the Blue “Rebuild Test” button. This will create a new sheet (called “Test (2)” – it will be silently overwritten if it already exists), insert the work columns, recreate all the formulas, replace the formulas by the values and finally drop the work columns. So, after all that work, the end result should be (and is) exactly what you started with.

(6) “TEST (SHORT)” is a bit more complicated, I'm afraid. You will not like it, but please try to understand what it's doing so we can make it fit your needs…

Usually, each of the formulas checks the Date (column A) in their row. If the Date is empty then the formula returns 0 (because there’s no valid data).

Unfortunately, lots of the formulas refer to cells on rows below them. If the row below is empty then any formula using it is meaningless and so these formulas return 0. This is what happened in "EOD_All_V1.xlsm".

However, I've now gone further than this. The formulas now check the furthest row below them that they use. If its Date is empty then they return 0.

Perhaps some examples will help….
K23's formula is now “=IF(A24=0,0,(I23*(2/(9+1))+K24*(1-(2/(9+1)))))”. So, if there’s no Date on row 24 then the formula will return 0.
G2's formula is now “=IF(A62=0,0,IF(ROW()<23,FORECAST(A2,G3:G62,A3:A62),E2*(2/(12+1))+G3*(1-(2/(12+1)))))”. Because the furthest down cell that the formula references is row 62, the formula returns 0 if that row's Date is empty.

Regards,
Brian.EOD-All-V2.xlsm
0
 
itjockey79Author Commented:
yes it is done i am  happy with this just one thing do i appy conditional formathing in 2 column (t & cell range B2:B21)..?
0
 
redmondbCommented:
itjockey79

it is done
I think that there's still a bit left to do!
 - I didn't think you'd be happy with the formulas checking dates so far down.
 - "process_new_data()" needs to be updated to handle the new layout.
 - Edit: ... and I need to add back in the code for handling multiple sheets!

Sorry, I don't understand the bit about the conditional formatting. If there should be some there then it needs to be included in "process_new_data()".

Regards,
Brian.
0
 
itjockey79Author Commented:
If it is possible then add conditional formatting otherwise no issue, it is negligent thing .Conditonal is only that i added in cell is if there is text ="SELL" then light
red in background & dark red as font & light green in back & dark green as fond if text contain "BUY"

formathing in 2 column (t & cell range B2:B21)
0
 
redmondbCommented:
itjockey79,

Focus, please - the other areas I mentioned are much more important!

Regards,
Brian.
0
 
itjockey79Author Commented:
about dates ? but in your previous post you provide one file which is working fine no "o" right so i assume that  you will rectify it .......
0
 
itjockey79Author Commented:
May sir request you one thing will pls look in to my question ZigZag RT, i gave up i dont find solution for how to get zigzag point in excel................pls sir


Thank you
0
 
redmondbCommented:
itjockey79,

about dates ? but in your previous post you provide one file which is working fine no "o" right so i assume that  you will rectify it .......
Sorry, what's "no "o" right"?

I was the first person to come up with a possible answer to your zig-zag question - but you never replied to it! How does my chart look now?

Regards,
Brian.
0
 
itjockey79Author Commented:
you are talking about open question?ZigZag RT



no zero in cells
0
 
itjockey79Author Commented:
ohhhhhh yeah i see you had posted on my zig zag question so sorry about ignoring you.....extremly sorry
0
 
redmondbCommented:
itjockey79,

No worries, you had a lot going on.

I may save you some time by saying that I had never heard of zig zag as a technical term, so my effort was mainly about producing a pretty chart line!

If you're not getting a good answer in Excel you might try a new question in a mathematical (or financial?) topic.

Now, can we get on with this question?!

Regards,
Brian.
0
 
itjockey79Author Commented:
yes pls provide me workbook now i am eagerly want to see ....pls?
0
 
itjockey79Author Commented:
yes i see your chart on zigzag it near to my need ,just want to little rectification : there is always points comes on rhythm of low ,high , low ,high.....i.e low low high is wrong & high high  low is wrong.



Thank You
0
 
redmondbCommented:
itjockey79,

Please see attached. You need to do a LOT of testing on this - for example, the formula in B2:B21 was wrong and you didn't find this in your earlier testing.

Regards,
Brian.EOD-All-V3.xlsm
0
 
itjockey79Author Commented:
so in EOD -ALL-V3.xlsm you had rectified formula B2:B21...? and in what area i have to focus on for testing...?
0
 
redmondbCommented:
itjockey79

you had rectified formula B2:B21...?
Yes.

in what area i have to focus on for testing...?
Everything. There are usually no formulas in the file - they are all created (and then dropped) by the macros. Therefore, you must check everything.

I have done testing - but, at best, that is just checking my understanding of what the file should do. To be crude, it's not my money at stake.

Regards,
Brian.
0
 
itjockey79Author Commented:
sure Sir i will run two copy of this process one is provided by you & one is manual for one week to cam pair ......thank you very much you helped me a lot ......one more thing sir pls about Zig Zag i dont find any standard solution for that will you pls sir......:)
0
 
itjockey79Author Commented:
Eagle Eye
0
 
redmondbCommented:
Thanks, itjockey79. Good idea about the testing. I keep an eye on "my" closed questions for at least a couple of weeks, so please feel free to post here if you have any queries/issues.

I'll have another look at the zigzag and if I get any ideas I'll post in that question.
0
 
itjockey79Author Commented:
cool sir little one more help will you add your code in this sheet pls as i dont touch code ,i am very conscious about that ........pls sir



Thank You
0
 
itjockey79Author Commented:
TOO BIG FILE I HAVE TO ATTACHED FROM OFFICE TOMORROW MORNING
0
 
redmondbCommented:
itjockey79,

I looked at your zigzag again. The problem is that there are lots of different zigzags but unfortunately you're looking for a specific zigzag - so unless you can identify the rules for your zigzag,  I'm afraid that there's not much I can do for you.

Regards,
Brian.
0
 
itjockey79Author Commented:
as per your chart zigzag it is ok just add one thing high - low high - low
0
 
itjockey79Author Commented:
sir i am stuck in between i cant run file pls look in to this
Capture.xlsm
0
 
redmondbCommented:
itjockey79,

It really isn't that easy! The chart below is one that I didn't send you as I felt that the other was "better". Yes, this one alternates highs and lows, but it's awful on the right=hand side. Also, look at the point at 11:05 - shouldn't this be at 11:25 instead?

My point is that I could (probably!) tweak my formula so that my zigzag looked exactly as you expect. However, the problem with this is that it almost certainly wouldn't work for your next set of figures - we need to know the rules!

V2b
Regards,
Brian.
0
 
itjockey79Author Commented:
Sir EOD file error pls look in to that i am very curious to run file.....

For Zig Zag  as far as all is good except last three point that are low low low not low high low...as i dint find specific procedure to pin point exactly  what i  want (as some point missed out in above chart)....in my charting software it is done perfectly & i have code for the same but i dont know any one understand or not...
0
 
redmondbCommented:
itjockey79,

Sir EOD file error pls look in to that i am very curious to run file.....
Please tell me the error, ideally including the file.

i have code for the same
Did you post this code in the original question?!

Regards,
Brian.
0
 
itjockey79Author Commented:
yes i had posted on that question not only for one charting software but 2 charting software & many links regarding that......


i had attached file, i made new one with whole data till today & i had copy past code in this file but it dosent work.as in your EOD V3 taking so much time to execute for one symbol i.e i had pasted data for say ACC  symbol for 1500 line in EOD sheet & i had clicked process new data it take 2 min & on going so i had closed it & decide to generate data in new file & copy past the code to that file...but still it is not working ...

pls see attached file....


if future date data is problem then delete from code i will do it manually that part


ThankYou
Capture.xlsm
0
 
itjockey79Author Commented:
As sir your previous code is much faster then this .....i had wait for 5 min for one sheet but still process is just going on ......i think future date data causing problem .......
0
 
itjockey79Author Commented:
copy data from any sheet & past in Eod up till 1500 row & click process new data button & see what is the problem why this is taking so much time........



Thank You
0
 
itjockey79Author Commented:
last post time is 15:02:40 process still going on for one symbol only.......i dint closed it want to see how much time it will take
0
 
itjockey79Author Commented:
This snap shot of final completed file.....
Capture.PNG
0
 
redmondbCommented:
itjockey79,

Your last post is not the file you sent me. It's absolutely essential that we're both talking about the same file so please be careful!

What spec is your PC? On my Intel Core i5 520M (2.4GHz) with 4GB of memory running Excel 2010 on Windows 7, the macro takes 65 seconds to run.

I tidied up some things...
(1) Your Company sheets had the Company name in B21.
(2) Your Company sheets had no conditional formatting (B2:B21).
(3) The sheets for the four Companies shown below appear to have been mis-spelt, so I changed the name of the sheets and the Company name in C21...
DR. REDDY'S
GRASIM IND
ITC LTD
TATA MOTORS
(4) I deleted the blank lines at the end of the "BB" sheet.
(5)  The blue button was pointing to an older version of the file. This would have caused big problems.
(6) When a company is added, the column headings are now formatted and the zoom is set to 90%.
(7) I cleaned up the process_new_data() macro...
   - I moved it from EOD (there was no good reason to have it there).
   - I moved the code for adding a new Company into a separate macro.

Please find attached two files...Capture-V2.xlsmCapture-V2-Output.xlsm

Please run process_new_data() in Capture_V2.xlsm and tell me how long it takes.

Regards,
Brian.
0
 
redmondbCommented:
(BTW, I see that the ZigZag columns is these files give completely different results to those in your other question!)
0
 
itjockey79Author Commented:
but where is blue button to click on EOD sheet (Capture-V2-Output.xlsm)


Windows 7 Enterprise - Service pack 1
Processor : Intel(R) Core(TM)2 Duo CPU E8400 @ 3.00GHz
RAM : 4.00 GB
64 Bit

Microsoft Excel 2010



i had run macro from view macro & run seems ok done in 60 odd sec
0
 
redmondbCommented:
itjockey79,

I restored the Ctrl-O you were using before. I dropped the button so you wouldn't have problems with it agan.

So, no probelms with the file now? BTW, it would be possible to speed it up, but at 60 seconds it's not worth the effort. However, the one thing I would do is to change the file from xlsm to xlsb -  just do a "Save As" And select "Excel Binary Workbook (*.xlsb)" as the Type. This will halve the size of the file and make its loading and saving more efficient.

Regards,
Brian.
0
 
itjockey79Author Commented:
Thank you very much Sir.redmondb.......may be it little for you but it is too lots for me.....now i can  concentrate on my other work........but one thing which pain me is Zig Zag indicator.....finger  cross & hope for the best..............i never seated & wait for other to work & find me solution , each & every day is my learning day,.......i had do trail & error in all of the questions.......too many things in my mind so some time all goes mixed up......sorry regarding that....
0
 
redmondbCommented:
No problem, itjockey79. All the best - but watch those formulas!
0
 
itjockey79Author Commented:
Really i dint noticed thank you for your Eagle Eye :) ,what in my is going about to calculate Zig Zag is (just thinking i dont know how to implement) find degree of each & every point from start point (first open price of whole set of data = start point),& then change in degree for consecutive 3 times in same direction then noted down that point as Zig High or low & same way for rest of the data....what you say sir am i thinking in right direction..?
0
 
redmondbCommented:
itjockey79,

It's really in your interest that any discussion on zigzag happen in that question. Please post the appropriate part of your last post there.

(Final thought here on zigzag - surely you, your company or your users must have at least one book on shares which describe zigzag?)

Regards,
Brian.
0
 
itjockey79Author Commented:
ok so now on i will post about zigzag on that question pls look in that with in 15min there is some thing to read ...


Thank You
0
 
itjockey79Author Commented:
ok sir content is read in zig zag question pls visit it as when you have time......


Thank you
0
 
itjockey79Author Commented:
Sir redmondb,

this time i want really really your help & i asked to you only as you can understand this as per my experience in EE.& you may rectify me if i am wrong on any step.

if you have time then pls see below link, it is generous request to you.
Scoring

Thank You
0
 
Naresh PatelTraderCommented:
Sir redmondb,

i am still stuck in, non of expert helping me, they told me to spit question which i had done & split to 3 question.but i am on very 1st question but still no result. will you pls look in to if you have spare time.

Scoring V 2

Thank You
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 47
  • 32
Tackle projects and never again get stuck behind a technical roadblock.
Join Now