Taking a Snapshot from an automatically updated Cell

j.hercksen

New member
Joined
Feb 20, 2014
Messages
8
Reaction score
0
Points
0
Hello there!

I am very new to forums, VBA, etc and would greatly appreciate some help with my problem.

I am piecing together a Profit/Loss Spreadsheet which is automatically updated by the Bloomberg Add-In. I input the price at which I enter a position and then input the price at which I would like to exit the position to take profit.

I want to be able to say something like "if the live price(updated automatically from bloomberg)> price at which I like to take profit, input the difference at that moment into another cell.
Basically, this snapshot is like closing the position and recording the profit into another cell.

Muchos Gracias!
 

j.hercksen

New member
Joined
Feb 20, 2014
Messages
8
Reaction score
0
Points
0
:eek2:Hello all,

I have made some progress on the aforementioned issue but still have a few things to iron out.

I decided to record a makro which copies the profit as a number into the realised profit column as soon as a certain criteria is fulfilled.


Code:
Function Makrostart() As String
Call Realized_Profit
End Function
--------------------------------------------------
Sub Realized_Profit()
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.Copy
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, -7).Range("A1").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveCell.Offset(0, 6).Range("A1").Select
Selection.ClearContents
End Sub

The formula behind the cell which should be delivered is as follows:

=IF(IFEMPTY(D4);"";(IF(D4="Short";IF(C4<=H4;Makrostart();IF(C4>=F4;Makrostart();"")); IF(C4>=H4;Makrostart();IF(C4<=F4;Makrostart();"")))))



The makro on its own works, however when combined with the formula I worked out in the spreadsheet it always comes up with a #VALUE error when one of the criteria is fulfilled.

Id really appreciate some help with this. If some further clarification is needed I can prepare a similar sheet that is accessible without bloomberg add-in.

Jannik
 
Last edited by a moderator:

patel

New member
Joined
Feb 20, 2014
Messages
60
Reaction score
0
Points
0
Location
Italy
Excel Version(s)
2010
can you attach a sample file ?
 

j.hercksen

New member
Joined
Feb 20, 2014
Messages
8
Reaction score
0
Points
0
Certainly. A few things to note. The current price column in the original is automatically updated but I have put some sample values in for accessability. The idea is when the current price crosses either the Stop Loss or Take Profit value, the Module "Realized_Profit" should kick in and
1. copy the unrealized profit value -> realized profit as a hardcode (no underlying equation)
2. delete the current price and the unrealised profit value

When running the Makro on its own, the abovementioned steps are completed with no problem but when testing the equation behind Colum J with an adjustment of Current Price into either Take Profit or Stop Loss territory, the #VALUE error comes up in Column J.
 

Attachments

  • Trade_Logbook_Sample.xlsm
    16.2 KB · Views: 15

patel

New member
Joined
Feb 20, 2014
Messages
60
Reaction score
0
Points
0
Location
Italy
Excel Version(s)
2010
you used a function without parameters, it does not make sense, you can not use activecell in function.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
PUt this code in the worksheet code module.

One thing to note, I have coded it to act if the price goes above the stop price or below the take Profit price. Should in be different for Short as against Long positions?

Code:
Private Sub Worksheet_Calculate()Dim cell As Range


    With Me
    
        For Each cell In .Range(.Range("C4"), .Cells(.Rows.Count, "C").End(xlUp))
        
            If cell.Value <> "" Then
            
                If cell.Value >= .Cells(cell.Row, "F").Value Or cell.Value <= .Cells(cell.Row, "H").Value Then
                
                    .Cells(cell.Row, "J").Value = .Cells(cell.Row, "I").Value
                    .Cells(cell.Row, "C").ClearContents
                    .Cells(cell.Row, "I").ClearContents
                End If
            End If
        Next cell
    End With
End Sub
 

j.hercksen

New member
Joined
Feb 20, 2014
Messages
8
Reaction score
0
Points
0
Hi Bob and Patel,

Many thanks for the responses. I cleared the code I had before and replaced it with the one that you gave me. Unfortunately, the error that now pops up when I try to manually change the current price to within stop loss/take profit territory is #NAME in the Realized profit column.

Also, should the first two lines not read:

Private Sub Worksheet_Calculate
Dim cell As Range


Bob, as for the short and long position, the only difference is the direction from where the current price comes from. So when each position is initiated, this would be the scenario:

Long Positions
Take Profit

Current Price

Stop Loss

Short Positions
Stop Loss

Current Price

Take Profit


In this sense, it would be ideal to write the code so that if the price goes above or below the boundaries set by the take profit and stop loss prices, the makro is triggered.

I hope this makes sense. :confused:
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
ARe you saying you get #NAME with my code?
 

j.hercksen

New member
Joined
Feb 20, 2014
Messages
8
Reaction score
0
Points
0
Yes. WHne replacing my previous code with yours realized profit comes with a #NAME error
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Can't see how that happens, I am not using any formulae or names. Can you post your workbook?
 

j.hercksen

New member
Joined
Feb 20, 2014
Messages
8
Reaction score
0
Points
0
Hi Bob,

Please see attached. I needed to hardcode Currenct Price and Take Profit as they were both linked with Bloomberg Add In and would have given you an error (unless you also have bloomberg).
 

Attachments

  • Trade_Logbook_Sample_2.xlsm
    24.8 KB · Views: 5

j.hercksen

New member
Joined
Feb 20, 2014
Messages
8
Reaction score
0
Points
0
Hi Bob,

Have you had a chance to look at the sample I included in the last email? Id really appreciate some feedback.
 

j.hercksen

New member
Joined
Feb 20, 2014
Messages
8
Reaction score
0
Points
0
Hi all,

Just realized that I had the code in a module and not in the actual code section. It works great for anything with a short position, however it needs to be tinkered with to accomodate long positions as the code is activated as cell value (market) is going to normally be larger than the stop loss in a long position.
 
Top