Newbie Help with VBA I think

[garretttpe]

Hi All
I have a spread sheet that tracks stocks and I need help. I need to be able to find the next sell date after a purchase and then recalculate after each find. Below is an example. On 1/2/15 the open v high was .10 cents therefore a sell would have taken place at each trigger, 0.02,0.03,0.04,0.05,.0.06, and 0.07 cents. On 1/5/15 there was only a .06 cent difference between open and high so a trigger at 0.07 would not have taken place that day and al others would have. this were is where I get lost. I need the formula to look at the open close low and high prices on the entire sheet (will be 255 rows) until it finds 7.63+.07 and put the value under the 0.07 column across from the date the trigger was found. in this case 2 days later. I need for this to happen continually for 255 rows. amytime a trade would have taken place I need a value of zero or leave the cell blank.
I have added my spreadsheet. You will see the sheet with the data and the next sheet is what I need it to look like and compute automatically.

Thank you all for any help on this. been kicking my but for 2 weeks straight.

Date	Open	High	Low	Close	open V high	Shares	Daily $$	Daily $$	Daily $$	Daily $$	Daily $$	Daily $$
							If sold	If sold	If sold	If sold	If sold	If sold
							$0.02	$0.03	$0.04	$0.05	$0.06	$0.07
1/2/2015	7.65	7.75	7.5	7.63	0.10	3922	$78.04	$117.25	$156.47	$195.69	$234.90	$274.12
1/5/2015	7.63	7.69	7.46	7.52	0.06	3932	$78.24	$117.56	$156.88	$196.20	$235.52
1/6/2015	7.6	7.72	7.48	7.71	0.12	3947	$78.55	$118.03	$157.50	$196.97	$236.45	$275.23
1/7/2015	7.78	7.8	7.68	7.76	0.02	3856	$76.74					$0.00
1/8/2015	7.82	7.9	7.79	7.85	0.08	3836	$76.34	$115.68	$154.24	$192.80		$268.16
1/9/2015	7.85	7.88	7.73	7.83	0.03	3822	$76.05	$114.27
1/12/2015	7.85	7.86	7.65	7.79	0.01	3822

[Yongle]

I read your post three times and still do not understand the question.
1 What exactly triggers next sale after purchase?
2 Are you only interested in the 0.07 column?

[garretttpe]

Good Morning and thank you for responding.
1. B4 is 7.65, F4 is .10 there for h4,i4,j4,k4,l4,m4, would be triggered as those are all less then F4 and the result is in those cells.
2. B5 is 7.63, f5 is.06 so only H5,i5,j5,k5,l5 would be triggered and M5 would not as .07 is greater then F5.
3. now I need to find when B5+.07(7.70) would have taken place within $b6$E255. and place the value (B5+.07)*G5 in the .07 column when that date is triggered.
4. I am interested in each of the H, I, J, K, L, M cells to do this.
I need to be able to see when a sell will take place based on the difference of open price and high price after a sell. then the next day will be a buy at open and then look again for a sell trigger, anytime H,i,j,k,l,m is greater then its the open to high difference.

I hope I am explaining this ok if not please let me know and I will try again.

Larry

[Yongle]

Ok - nearly there, I think.

Hi Larry - I think I now understand, thank you for your patience.

Question 1 - is all this correct?
Each day if colF > 0.07 then calculate all values in columns H to M at 0.02 to 0.07

If colF < 0.07, value in Column M = blank (this happens on Jan 05)
If colF < 0.06, value in Columns L to M = blank
If colF < 0.05, value in Columns K to M = blank etc

On Jan 05, cannot sell at 0.07, check for next possible sell. When is price next > 7.70 (Jan 05 Open + 0.07)
Jan 06 High = 7.72 (which is > 7.70) so this is next sell and we put G5 X .07 into cell M6 (because we bought them at open price B5

Question 2 - (Please answer this question ONLY if Question 1 is correct)
On Jan 06 value in column F = 0.12 which > 0.07 and all conditions for Jan 06 are satisfied, so cell M6 would be G6 X 0.07

Which is correct ?
M6= (G5 X 0.07) OR
M6 =( G5 X 0.07) + (G6 X 0.07) which allows for selling Jan 06 and Jan 05 shares at 0.07 on this day

Yon

[garretttpe]

Hi Yon
1 is correct
2. Almost there, Jan 5th condition for M5 was meet on Jan 6th due to C6 being greater than (b5+0.07) not F6 being >0.07. which allows selling G5 shares at 0.07 and value in M6

I cannot thank you enough for working through this with me.
Larry

[Yongle]

Ok Larry

So now to Question 2
On Jan 06 value in column F = 0.12 which > 0.07 and all conditions for Jan 06 are satisfied, so cell M6 would be G6 X 0.07

Which is correct ?
M6= (G5 X 0.07) OR
M6 =( G5 X 0.07) + (G6 X 0.07) which allows for selling Jan 06 and Jan 05 shares at 0.07 on this day

Yon

[garretttpe]

Hi Yon
Looks like M6=(G5 x 0.07)
thanks again

Larry

[Yongle]

Ok - that is fine. I understand now.

Last question - what happens to Jan06 stock that would have been in cell M6 (=G6 X 0.07) - do we just ignore this?

thanks
Yon

[Yongle]

Hi Larry

I think you want something like this.

I have assumed that you are going to run this macro after all the values are already in the cells from your various formulas. What I did was to copy columns A to M to sheet1 and ran the code against that sheet - so you will need to amend the first line of code if you want to use a different sheet.
I was not sure if you wanted a formula in the cells being overwritten or values - I think you would probably prefer to see the formula - so that is what I have done.
The vba looks for Zero values in columns H to M and then looks for the first time that sell conditions are met and overwrites in correct cell in columns H to M

To make it a little easier to see what is going on with all those numbers, I have included 3 lines that you can remove once you are happy the code is doing what you want it to do.
- The zero value cells in rows H to M are highlighted and also cells amended by the next sell are highlighted in a different color.
- There is a comprehensive message box that informs you exactly where the values are coming from, so that it makes it easy to check the logic. The box will pop up every time a cell is being amended. This may be useful for the first few entries but very tedious after that. At the moment there is an apostrophe in front to stop it running. To switch it on just remove the apostrophe. When you get bored of seeing the messages you can hit ctrl + break to stop the code running. I found it very useful when I was testing the code.

One thing I have noticed is that that there are times when a previous cell overwrite is then overwritten again because the price on the day satisfies the conditions to trigger a sell more than once. If this is what you want the spreadsheet to do then that is fine. (It looks odd that these potential sells "disappear" - but I have treated them in the same manner as what we discussed yesterday.)

If anything is unclear or if you need any help amending anything please shout.
Any problems running the code and I could send your original workbook which now incudes the VBA - but I am sure you would rather have a go yourself first. I hope this helps you make a big profit an selling stock. /
Yon

Sub NextSell()

Sheets("Sheet1").Select       
    'set up variables
    Dim Shares As String, Margin As String, CellRef As String
    Dim r As Integer, c As Integer
    
    For r = 4 To 255
    For c = 8 To 13
    
    If Cells(r, 2).Value = 0 Then Exit Sub  'ignore later cells with no purchases
        If Cells(r, c) = 0 Then
        ' highlight cells where no sales
            Cells(r, c).Interior.Color = RGB(250, 200, 150)  'Can delete this line 
            Price = Cells(r, 2).Value
            MinSellPrice = Price + Cells(3, c).Value
                For r2 = r + 1 To 255
                    If Cells(r2, 3) >= MinSellPrice Then
                    'highlight cells where condition is met for first time
                        Cells(r2, c).Interior.Color = RGB(200, 200, 200) ''Can delete this line 
                        Shares = Cells(r, 7).Address
                        Margin = Cells(3, c).Address
                        
        'Can delete the next 4 lines
       'MsgBox Format(Range(Shares).Value, "0,000") & " Shares" & vbNewLine _
        & "Bought on " & Cells(r, 1).Value & vbNewLine & "Cost $" & Format(Price, "0.00") & vbNewLine & _
        "Margin " & Range(Margin).Value & vbNewLine & "Minimum selling price $ " _
        & Format(MinSellPrice, "0.00") & vbNewLine & "Achieved on " & Cells(r2, 1).Value
       
                        Cells(r2, c).Select
        'put formula in cells when stock can be sold
                        CellRef = "=" & Shares & "*" & Margin
                        ActiveCell.Formula = CellRef
                        r2 = 255
                Else
        End If
        Next r2
    Else
    End If
    Next c
    Next r
    
End Sub

[garretttpe]

Good Morning Sir
Sorry for the delay getting back to you, my work has me straight out sometimes. I cant thank you enough for the time and help you have given me, a complete stranger. I have one more important question. How the heck do I get this to run?? I have excel 2010, I opened VBA and copied the formula to sheet 1, then when I try to run Macro it says script out of range. Is there anyway you can send me the file with everything running. I am afraid when it comes to this stuff I am pretty much lost. I am ok with excel but this is way over my head.

Larry

[Yongle]

Hi Larry

Here is the file.
When you open click to enable macros and then hit ctrl + shift + s to run the macro

The macro is currently set to run with the message pop-up - I think you will find that useful. Like I said (previous post) when you get bored of clicking OK, use ctrl + break to stop the code running. And then remove the apostrophe.

There is also a second macro which is useful - a quick eraser - this deletes everything in sheet1 and copies the original values back . A full reset is something that I find essential when testing code. With VBA if you change something and run things from where you last fiddled, it bites you in the butt sometime later when you run the code from a clean start. Anyway use ctrl + shift + v to rub everything out and start again. (no need to run this before running other macro, but may be useful if you are doing any other experimenting in sheet1

As to your questions - my own workbooks have been tamed long ago and are therefore now less interesting. I enjoy new puzzles. Your Excel requirements (and everyone else's) are different to mine. My VBA skills can only be honed by trying new code. VBA becomes a bit addictive. We all gained from the help of others when we started too, and those first tentative steps are absolutely the hardest.

Anyway - I hope the numbers come out the way you expect them to.
Yon

[garretttpe]

Good morning Yon
I jumped the gun on my last email, I was able to get everything working. unfortunately there is still a issue with the CODE. if you look at the Rite Aid Result tab you will see what the results need to be. the code as it stands start to work then fills in sell triggers when no triggers are set.

[Yongle]

Hi Larry
I am clearly being a bit slow on the uptake this morning, and I am sure it is absolutely nothing to do with that excellent ale that needed consuming urgently before it went stale last night.
You know what you expect in the end result, and I do not - I am looking hard but not seeing! So can you help me out and be very specific and then we will get pretty quickly.

If you start the code running with the message box enabled until it you hit your first problem then tell me which cell it is and why there should not be a trigger at that point. Can you check all the entries in the message box and confirm that all is ok there. Can you then keep keep hitting OK until the you get to the next 2 incorrect triggers and do the same.

Thanks
Yon

[garretttpe]

Hi Yon

must be nice I am at a place where there is no alcohol. soon though I will be home in the states and then ale time it is.
1.M9 is a value and it should be zero. F9 is less then .07 so M9 must be zero
2.H11 needs to be zero. Reason- stock was bought on A10 due to sell on H9, no trigger on H10(F is less than .02) so the next open, high, low, close value that is A10=.02 is on D31. so I need H31 to be .02*g10.

Larry

[Yongle]

Ok - will look at this after a typical Sunday lunchtime libation in honour of Bacchus, not that I am trying to make you jealous or anything...
Yon

[Yongle]

Larry
What you are seeing does not match what I am looking at. I have run the macro repeatedly and always get the same result but I have always cleared the data out and started afresh with the original data before re-running the macro whilst I was testing. There is a big clue in that sentence! I think we have got there.

Can you open the spreadsheet that I sent you in post#11 and run the macro from within that (using ctrl + shift + s).

Below is the output that I always get and looking at the 3 cells (in red) that you mentioned:
M9 (9 January) is zero - just what you expected
H10 (12 january) is zero - just what you expected
H31 (11 February) is G10 X H3 - just what you expected

Regards
Yon

Date	Open	High	Low	Close	open V high	Shares	Daily $$	Daily $$	Daily $$	Daily $$	Daily $$	Daily $$
							If sold	If sold	If sold	If sold	If sold	If sold
							$0.02	$0.03	$0.04	$0.05	$0.06	$0.07
02/01/2015	7.65	7.75	7.5	7.63	0.10	3922	$78.04	$117.25	$156.47	$195.69	$234.90	$274.12
05/01/2015	7.63	7.69	7.46	7.52	0.06	3932	$78.24	$117.56	$156.88	$196.20	$235.52	$0.00
06/01/2015	7.6	7.72	7.48	7.71	0.12	3947	$78.55	$118.03	$157.50	$196.97	$236.45	$275.23
07/01/2015	7.78	7.8	7.68	7.76	0.02	3856	$76.74	$0.00	$0.00	$0.00	$0.00	$0.00
08/01/2015	7.82	7.9	7.79	7.85	0.08	3836	$76.34	$115.68	$154.24	$192.80	$231.36	$269.92
09/01/2015	7.85	7.88	7.73	7.83	0.03	3822	$76.05	$114.27	$0.00	$0.00	$0.00	$0.00
12/01/2015	7.85	7.86	7.65	7.79	0.01	3822	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
13/01/2015	7.8	7.83	7.43	7.57	0.03	3846	$76.54	$115.00	$0.00	$0.00	$0.00	$0.00
14/01/2015	7.4	7.59	7.21	7.55	0.19	4054	$80.68	$121.22	$161.76	$202.30	$242.84	$283.38
15/01/2015	7.56	7.6	7.27	7.34	0.04	3968	$78.97	$118.65	$158.33	$0.00	$0.00	$0.00
16/01/2015	7.29	7.41	7.22	7.4	0.12	4115	$81.89	$123.05	$164.20	$205.35	$246.50	$287.65
20/01/2015	7.48	7.49	7.16	7.32	0.01	4011	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
21/01/2015	7.3	7.43	7.23	7.32	0.13	4110	$81.78	$122.88	$163.97	$205.07	$246.16	$287.26
22/01/2015	7.39	7.49	7.28	7.45	0.10	4060	$80.78	$121.38	$161.98	$202.57	$243.17	$283.76
23/01/2015	7.44	7.58	7.43	7.51	0.14	4032	$80.21	$120.32	$160.43	$200.53	$240.64	$280.75
26/01/2015	7.49	7.66	7.46	7.65	0.17	4005	$79.71	$119.76	$159.81	$198.41	$238.10	$277.78
27/01/2015	7.55	7.65	7.53	7.58	0.10	3974	$79.07	$118.81	$158.54	$198.28	$238.01	$277.75
28/01/2015	7.61	7.66	7.39	7.42	0.05	3942	$78.45	$117.87	$157.29	$196.71	$236.14	$0.00
29/01/2015	7.45	7.47	7.06	7.26	0.02	4027	$80.13	$0.00	$0.00	$0.00	$0.00	$0.00
30/01/2015	7.2	7.24	6.98	6.98	0.04	4167	$82.92	$124.58	$166.25	$0.00	$0.00	$0.00
02/02/2015	7	7.08	6.68	7.05	0.08	4286	$85.29	$128.14	$171.00	$213.86	$256.71	$299.57
03/02/2015	7.09	7.32	7.09	7.29	0.23	4231	$84.20	$126.52	$168.83	$208.33	$250.00	$291.67
04/02/2015	7.21	7.39	7.17	7.28	0.18	4161	$82.80	$124.41	$166.02	$207.63	$249.24	$290.85
05/02/2015	7.3	7.35	7.24	7.35	0.05	4110	$81.78	$122.88	$163.97	$205.07	$246.16	$0.00
06/02/2015	7.4	7.48	7.35	7.35	0.08	4054	$80.68	$120.81	$161.76	$202.30	$242.84	$287.67
09/02/2015	7.32	7.4	7.28	7.39	0.08	4098	$81.56	$122.54	$163.52	$204.51	$245.49	$286.48
10/02/2015	7.43	7.6	7.41	7.58	0.17	4038	$80.35	$120.73	$161.07	$201.34	$241.61	$281.88
11/02/2015	8.25	8.48	7.92	8.08	0.23	3636	$76.43	$114.65	$153.85	$192.31	$230.77	$275.95
12/02/2015	8.22	8.22	7.91	8.09	0.00	3650	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
13/02/2015	8.11	8.4	8.08	8.34	0.29	3699	$72.99	$109.49	$145.99	$182.48	$218.98	$255.47
17/02/2015	8.37	8.38	8.15	8.2	0.01	3584	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
18/02/2015	8.18	8.22	8.15	8.19	0.04	3667	$72.98	$109.66	$146.33	$0.00	$0.00	$0.00
19/02/2015	8.16	8.22	7.96	7.99	0.06	3676	$73.16	$109.93	$146.69	$183.46	$220.22	$0.00
20/02/2015	8.01	8.17	8	8.15	0.16	3745	$74.53	$111.99	$149.44	$186.89	$224.34	$261.80

[garretttpe]

Hi Yon
yes the red indicators are correct but all cells in between need to be blank or 0.00 on 12/01/15 the open price was 7.85. it wasn't until 11/02/15 when a sell should be triggerd. 7.85+.02 (7.87) happened on the open so the red as indicated would be correct. all cells in between needs to be blank or 0.00.

thanks
Larry

[Yongle]

Hi Larry

You may have you heard the expression "two nations divided by a common language" being applied to us in the UK and you guys over the pond. What an appropriate expression here!!
Here is an explanation of what the code is doing and hopefully you can tell me what you really want it to be doing, and this time I will do my very best to hear the same thing you are saying.

1 Your formulas calculates everything that go into columns H to M
2 The macro then looks at every cell H4 to M255 looking for value = 0 (as calculated by your formulas) and takes the Open price that day and adds the appropriate Column H to M margin to that to value before comparing it with the subsequent entries in the High price column until it finds a value that will satisfy the relevant condition.
3 When it finds that value, it then puts an entry in the relevant column (H to M) for that day overwriting what was there before
4 It does not make any difference to the result but it goes through that process starting at H4, then I4, J4 K4, L4 to M4 then onto row 5 etc
So far so good.....I think....
At this point every cell is either where your formulas left it, or as overwritten by the macro process explained above.
I'm happy, this is what I expected BUT you are puzzled, it is not what you expected.

So let's try one more time. What would you like the macro to do next?
Given our previous communication mismatch, it may be easier if you could describe it in the way a macro would approach it ( ie try to step through which value needs to be changed, and how that is determined - what it needs checking against, what is the condition that needs to be met and what the new value should be.)
And finally....maybe... but I somehow doubt that... - what is the difference between a blank cell and a zero value cell, and what makes that happen - at the moment the zero value cells are there because that is what your formula has put there and there is nothing in the macro to change anything to a blank.

I will be working this challenge out of my system during the next 2 -3 hours at the gym...
I look forward to your reply.
regards
Yon

[garretttpe]

Hi Yon
I wil try my best again. a blank cell or 0.00 cell is the same to me. this is for column H result only .02 cents
On 12/1/15 a buy was done at 7.85 (B10), no sale at H10 (0.02) due to F10 being 0.01. Now I need to look at all the open, high, low and close prices from row 11 to 255 until 7.87 or greater is found (7.85+.02). that happened on ROW 31 (B31). Since I bought 3822 shares on 12/1/15 (B10) I need to multiply .02*3822 and the result needs to be in H31 and all cells from H10 and H30 will be 0.00 or blank don't matter to me.

eventually I need the same thing to happen in I, J, K, L, M, also with their perspective prices, .03,.04,.05,.06,.07.

I wont be back on until tomorrow AM. I am Temporarily in Jordan so I am not to far off time wise from you.

Thanks Again
Larry

[Yongle]

Hi Larry

What an exotic life you seem to lead. Probably sunnier there too than Cornwall on a winter's day!!
Back to the more mundane....

"Sell"
Our previous checks suggest this is triggered correctly but (for the sake of your sanity and mine!) let's be absolutely certain
Jan 09 - cols J to M not triggered because "High" below $7.89
Feb 11 - cols J to M triggered because "High" exceeds $7.89, $7.90, $7.91,... $7.94
Feb 11 - write margin values to Cols J to M based on the number of shares bought on Jan 09 (col G on Jan 09)
Fundamental question - is this correct?

"Buy"
Our macro leaves all values as per your formulas unless we tell it otherwise.
We have not told it not to buy - so the values in cols H to M are either your formulas or the result of the "sell" process above
So the next step is to tell the macro when to buy or when not to buy

What I need from you
I do not want to know what to put in any cell, I need to understand the process. Please try not to refer to any cells in your explanation.
After Jan 09 what triggers the next buy?
On Jan 10, if there is not a "buy" why not (for example - is it because we have not yet been able to sell Jan 09 purchases with margin 0.03 or because we have not sold Jan 09 purchases with margin 0.07, or some other condition)
On Feb 11 (if this is the next "buy") what is the trigger - what is the condition that must be met?

thanks
Yon