Posts by KJ Kelly

*

By The Cringe,

As requested I have started a new thread as the existing one (referenced above) is closed.

In the solution on your spreadheet (column N of the attached spreadsheet) in your last post you used the function
=IF(AND(K7,J7=0),1,IF(J7,0,N6)) for cell N7.

This provided the solution I needed.

Now I want to take the model further and I need to amend the function but I don't understand it well enough. I have looked in Excel help and on the web but without success.

Specifically, I have inserted two new columns containing additional criteria and need to incorporate these in the above function in column N.

I have attached a sample spreadsheet showing the existing formulae and have highlighted the new columns in red. I have also put in red one sample additional trade the function needs to generate, cell O18.

Currently, the "buy" in column O arises as the buy signal in column J occurs after a sell signal in column K, e.g. J10. J14 does not trigger a "buy" as it follows a buy signal and the trade already exists.

Now that I am testing a different close out for trades, column L and M, cell J18 needs to trigger a buy as it follows a sell signal in cell M17.

Please let me know if I need to provide more information.

Re: Find & Return Weekly High Value

Yes, that's what I did.

This works fine in the example spreadsheet.

When I do this in the model spreadsheet, it puts the value in L3, of the high from the week ending on 17 Aug 2007, row 1027. It starts from that date!

I have done some further testing and found that it works fine when I use a row value of up to 250 but not, for example at 260 and beyond.

Am I missing something very simple?[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]I have checked the spreadsheet to see if there were any formatting issues that could have affected the calculations. Everything was OK. Also, I have inserted more data into the 1400 rows in the example spreadsheet for testing. Then the same probelm occurred - the formula only worked up to row 250.

Re: Find & Return Weekly High Value

Sicarii thanks for helping me.

This is a completely new area for me and I did not have the add-in Analysis Toolpak installed.

It is installed now and your solution works perfectly in the example spreadsheet.

In my actual model I, have tried, but don't know how to adapt the array formula for the quantity of rows having data. I have five years data being 1276 rows of daily data which convert to 264 rows of weekly data on my spreadsheet. The example spreadsheet was an extract from the actual one.

As I say this is a new area for me and any guidance you can give me would be great.

Re: Find Weekly High Value And Copy

Sicarii, thanks for your reply. I have to admit this is way beyond my knowledge of excel formula.

I am having difficulty in getting this to work in the example spreadsheet. After entering the formula with <CTRL>+<SHIFT>+<ENTER> in the cells L3 and L4 I just get #NAME? in each cell.

Re: Find Weekly High Value And Copy

This is now the example spreadsheet.

I want to find the high value from daily data for each week and copy that value.

I have a list of daily price data, being the date, opening price, high price, low price and closing price. For each week I am finding the data necessary to create a list of the weekly opening price, high price, low price and closing price ending on each Friday.

To find the opening price I am using this formulae -
=INDIRECT(ADDRESS(MATCH(J2,$A$1:$A$1400,0)+1,COLUMN(B1)))

To find the closing price the formulae is -
=INDIRECT(ADDRESS(MATCH($J$3,$A$1:$A$1400,0),COLUMN(E2)))

I need to find the high value and the low value within the five days data, from two separate columns (C and D) of daily data.

Can I use a formula similar to those above, but instead of copying a relevant cell, for example, finding the high from cells B3 to B7. Then I need to do this for each subsequent week.

An example spreadsheet is attached. You will see that currently I am currently copying the Friday data in columns L and M as I don't know how to adapt the formula to do the job.

Re: Find Intermittent High Value

Hi, I have tested the formula in my model and in the sample spreadsheet on this thread and it is producing #VALUE! in the relevant cells.

Les

Re: Find Intermittent High Value

Hi Derk,

Thanks for your suggestions for speeding up the macro. It really does improve the speed on the previous version.

This macro and my previous versions are running slower on this latest model than on another one but I guess that is down to me. I have used the framework of the other model to develop this latest one and have obviously done something to cause it to run slower.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Hi Krishnakumar,

Many thanks for the formula based solution; I hadn't realised that this was possible!

The second part of the macro contains a routine to find the lowest low values. I'm afraid I haven't got the expertise to develop your formula further to find a formula based solution. Is it possible for a formula to solve this problem?

Les

Re: Macro Will Not Run Correctly From &quot;guide&quot; Sheet

Hi rb,

The descriptions you gave are correct in that is what the macro does.

In running your amended macro from my "Guide" or my "Data" sheets nothing happens. If I run it when I have on screen one of the sheets in the array, it runs on that sheet only and does not move on to the other sheets in the array.

Having the following code within the body of the macro slows down the calcalation on the one sheet that it runs on.

LastRow = .Range("J65536").End(xlUp).Row

Re: Macro Will Not Run Correctly From &quot;guide&quot; Sheet

OK, this is checking

- two colums to find if a sell trade was triggered, i.e. a value > 0 exists in cells
- if so copy sell price for that trade and paste
- then check if a lower price occurred before the date of the sell
- if so copy that lowest price into cell three to the right

The following macro does what it is designed to do and needs to be run from a control sheet called "Guide".

When I run it from this Guide sheet it stops at around row 53 (out of 1400 rows) on each of the specified sheets in the macro.

I have struggled with this problem and have now discovered that the macro will run correctly when run from one of the worksheets specified in the array, e.g. sheet "200 and 100".

Can anyone help me solve this problem and see what is causing this problem?

Sub Calculateclosingtrades1()
     
Dim r As Long, c As Integer, LastRow As Long, rcheck As Long
LastRow = Range("J65536").End(xlUp).Row
    Dim shtTemp As Worksheet
    Dim vntName As Variant
     
For Each vntName In Array("200 and 100", "100 and 50", "50 and 25", "40 and 20", "20 and 10", "15 and 10", "18 and 9", "200 only", "100 only", "50 only", "40 only", "25 only", "20 only", "15 only")
    Set shtTemp = Worksheets(vntName)
         
    shtTemp.Select
    shtTemp.Range("N30").Select
       For r = 30 To LastRow
           If Cells(r, 13) > 0 Then
                For rcheck = r To LastRow
                    For c = 10 To 12 Step 2
                        If Cells(rcheck, c) > 0 Then
                            Cells(r, 14) = Cells(rcheck, c)
                            Cells(r, 16) = Application.Max(Range(Cells(r, 6), Cells(rcheck, 6)))
                            c = 12
                            rcheck = LastRow
                        End If
                    Next c
                Next rcheck
           End If
       Next r
    Next


Sheets("Guide").Select
End Sub

Re: Find Intermittent High Value

Thanks for your help. I have now got the code to run through all of the 300 rows on each sheet. I am not a programmer and don't know how to check that the LastRow is properly set. The code that works is:

Sub Calculateclosingtrades1()
     
Dim r As Long, c As Integer, LastRow As Long, rcheck As Long
Dim xmin As Double
LastRow = Range("j65536").End(xlUp).Row
Dim shtTemp As Worksheet
Dim vntName As Variant
     
For Each vntName In Array("4 and 4 Weeks", "4 and 3 Weeks", "4 and 2 Weeks", "4 and 20 DMA", "4 and 20 Line DMA")
Set shtTemp = Worksheets(vntName)
         
shtTemp.Select
    Range("p20").Select
        For r = 20 To LastRow
        If Cells(r, 16) > 0 Then
            For rcheck = r To LastRow
                For c = 11 To 13 Step 2
                    If Cells(rcheck, c) > 0 Then
                        Cells(r, 17) = Cells(rcheck, c)
                        Cells(r, 19) = Application.Max(Range(Cells(r, 10), Cells(rcheck, 10)))
                        c = 13
                        rcheck = LastRow
                    End If
                Next c
            Next rcheck
        End If
    Next r
   


    Range("t20").Select
        For r = 20 To LastRow
        If Cells(r, 20) > 0 Then
            xmin = 1E+300
            For rcheck = r To LastRow
                If Cells(rcheck, 11) > 0 And Cells(rcheck, 11) < xmin Then xmin = Cells(rcheck, 11)
                For c = 10 To 12 Step 2
                    If Cells(rcheck, c) > 0 Then
                        Cells(r, 21) = Cells(rcheck, c)
                        Cells(r, 23) = xmin
                        c = 13
                        rcheck = LastRow
                    End If
                Next c
            Next rcheck
        End If
    Next r
Next
Sheets("Guide").Select
End Sub

I'd appreciate any advice on how to speed things up. I have upgraded to Excel 2007 and this macro chugs through like a steam engine rather than an electronic one. Some of this is due to Excel 2007 I think!

Re: Find Intermittent High Value

Derk,

I have put the macros into the model I'm developing and they work just fine.

Realising that I did not need two macros per sheet and a separate macro to run them all, I have combined them into one macro. This macro continues to provide the correct results but ceases around row 53 on each sheet. I'm fairly sure that the new code is not causing this problem but for the life of me I cannot see why it stops at this row and then moves to the next column/sheet.

Sub Calculateclosingtrades1()
     
Dim r As Long, c As Integer, LastRow As Long, rcheck As Long
Dim xmin As Double
LastRow = Range("J300").End(xlUp).Row
Dim shtTemp As Worksheet
Dim vntName As Variant
     
For Each vntName In Array("4 and 4 Weeks", "4 and 3 Weeks", "4 and 2 Weeks", "4 and 20 DMA", "4 and 20 Line DMA")
Set shtTemp = Worksheets(vntName)
         
shtTemp.Select
    shtTemp.Range("P20").Select
    For r = 20 To LastRow
        If Cells(r, 16) > 0 Then
            For rcheck = r To LastRow
                For c = 11 To 13 Step 2
                    If Cells(rcheck, c) > 0 Then
                        Cells(r, 17) = Cells(rcheck, c)
                        Cells(r, 19) = Application.Max(Range(Cells(r, 10), Cells(rcheck, 10)))
                        c = 13
                        rcheck = LastRow
                    End If
                Next c
            Next rcheck
        End If
    Next r


    shtTemp.Range("T20").Select
        For r = 20 To LastRow
        If Cells(r, 20) > 0 Then
            xmin = 1E+300
            For rcheck = r To LastRow
                If Cells(rcheck, 11) > 0 And Cells(rcheck, 11) < xmin Then xmin = Cells(rcheck, 11)
                For c = 10 To 12 Step 2
                    If Cells(rcheck, c) > 0 Then
                        Cells(r, 21) = Cells(rcheck, c)
                        Cells(r, 23) = xmin
                        c = 13
                        rcheck = LastRow
                    End If
                Next c
            Next rcheck
        End If
    Next r
Next
Sheets("Guide").Select
End Sub

Can you help me in this pl?
Les

Re: Find Intermittent High Value

Thanks, absolutely brilliant.

Regards,
Les

Re: Find Intermittent High Value

Your solution works perfectly, many many thanks.

I'm trying to adapt the line of code to seek the minimum value in a range but am getting a zero all the time.

For example, using the same spreadsheet as an example, with the following code a 0 is always produced:

Cells(r, 19) = Application.Min(Range(Cells(r, 10), Cells(rcheck, 10)))

I realise that 0 is the lowest number but if I replace the zeros with blanks it messes up the rest of the sheet. Is there any way around this problem?

Best regards,
Les

Re: Find Intermittent High Value

In summary, I have ranges of different lengths in a spreadsheet where I need to find the highest value in each range and copy and paste that value.

Re: Find Intermittent High Value

Sorry, if I'm not clear but I think it's best to go to the spreadsheet "Example" attached to my first post for me to be able to illustrate the problem.

Based on the spreadsheet example I need a function (or macro) that will find the value of 485 that needs to be inserted into cell S17. For example:
If Q17>0,select range from J17 to J37 and find the highest value to insert into cell S17.

This is just an example as when a value occurs in column Q the required range to find the highest value will always be of different length. It will depend upon the closing trade in column Q, which in turn is derived from the next value >0 in column N.

I hope this is clearer.

Regards,
Les

Re: Find Intermittent High Value

Thanks for your reply.

The problem, as I see it, is that the values in column Q are not known until I run the existing macro. Only then is the range known for column K. This range will always be different moving down the spreadsheet, so I am having problems in creating a function that will find the highest value.

I am analysing market data to determine when trades should take place and when they should be closed. This works well. But I need to find the intermittent high value between the dates for purchase and the closing trades, and then insert these values in column S in the attached worksheet.

I don't know if a function can find these values or if a macro is needed (or if I should amend the existing macro that currently finds the
closing trades).

This macro is included in the attached spreadsheet. It Is:

Sub CloseBuy44()
Dim r As Long, c As Integer, LastRow As Long, rcheck As Long
LastRow = Range("j65536").End(xlUp).Row
 
For r = 4 To LastRow
   If Cells(r, 16) > 0 Then
       For rcheck = r To LastRow
           For c = 11 To 13 Step 2
               If Cells(rcheck, c) > 0 Then
                   Cells(r, 17) = Cells(rcheck, c)
                   c = 13
                   rcheck = LastRow
               End If
           Next c
       Next rcheck
   End If
Next r
End Sub

In the attached sheet I have inserted in red the values I need find for column S. The period of weeks I need to search for these values is not always the same and they do not always occur.

This is a difficult problem, for me at least, and any help will be really great.

Re: Result Conditional Upon Preceding Values

Thanks, that seems to have done the trick. I need to continue testing but am having problems in getting Excel 2007 to recognise the last sheet in my workbook. Why did I ever move up from Excel 2000!! In fact I am inclined to reinstall it. Excel 2007 is so slow.