way to calculate the drawdown for a futures trading account’s equity. The following is the record of taking a sequence of 20 trades. Drawdown is defined as the lowest point between two consecutive equity highs. Therefore a formula should detect pairs of equity highs and then find the maximum difference between these highs and the lowest point in between each of these pairs. Do you think this can be done using standard excel functions?
You might wish to plot a linear graph to visualize the equity. Looking forward to hear what you think! I include more examples of equity records.
formal definition of a drawdown:
Drawdown: The magnitude of a decline in account value, either in percentage or dollar terms, as measured from peak to subsequent trough. For example, if a trader's account increased in value from $10,000 to $20,000, then dropped to $15,000, then increased again to $25,000, that trader would have had a maximum drawdown of $5,000 (incurred when the account declined from $20,000 to $15,000) even though that trader's account was never in a loss position from inception
This is what i am trying to do when the balance increases by $1000 the lot size should move to the next level e.g 0>1000=0.01 1000>2000=0.02 and for each movement the value of the 0.01 should be 0.09 e.g. 0.01=0.09 0.02=0.18 0.03=0.27 etc.
I'm trying to come up with a formula that allows me to insert the date of the previous trading day. Because for several reasons I can't create a column array of manually entered holidays, I'm trying to find a formula that would produce this. I've tried this, but excel doesn't like it.
The attached spreadsheet should explain my issue. In a nutshell, the value on line 6 is from the previous day's data (i.e Tuesdays line 6 is based on Monday's data). If the previous day has no data ( i.e. market closed), line 6 should reflect the last line 6 calculation.
Also, i would like line 6 to be zero past 2 calendar days from today. i.e. Friday June 6 should read zero until tomorrow (june5) data is entered.
I'm brand new to programming in Excel, and currently building a system that will help me trade equity options (similar to trading stocks). So far, I've put together the ranking system I want to use based on a live data feed that updates in real-time (during market hours). For every possible trade in my 'universe' (currently about 600), Excel provides a live score (based on my formulas and weightings) as well as prices and other info about the trade (options strikes, symbols, expected return, etc.).
What I'd like Excel to be able to do is keep a separate sheet where a trade (row) that scores over an arbitrary threshold, say, 100 'points,' is recorded as a static line (instead of continuing to dynamically update), like a snapshot of that line. I'll use this list of trades to submit to my broker for trading.
If it was as simple as a 'triggered' copy - paste special - values, that would do just fine. The trigger would also need to see if that trade has already been recorded for the day, so I don't get an infinite list of the same trade.
After a trade makes it into this list, I'll need to compare it to what I already have in my portfolio (which I'll also need to build) so I don't become too heavily weighted in any one trade. (Just thought I'd let you know where this will be going, too) ...
I was looking for some code to randomly generate a number for a prize draw I am doing. I found a great piece on this site, which I have slightly changed to give me what I have below.
It works great except for one thing I cannot work out, I have the code below working on cell "C1", I would like it to work in cell "C5", but I can't seem to work this out.
Sub SlotsNum() Dim w As Long, x As String, y As Long Randomize x = Format(Int((Range("B2") - Range("B1") + 1) * Rnd + Range("B1")), "0000") For y = 1 To 1 For w = 1 To 250 Range("C" & y) = Int(8000 * Rnd) Next w Range("C" & y) = CInt(Mid(x, y, 4)) Next y End Sub
Also, whilst here. i will have one other problem, with it being a prize draw I do not want to draw the same number twice. At the moment I have a small macro that copies the winning name that appears on the main screen (via a vlookup) onto a winners spreadsheet. I am thinking of then creating another macro that replaces the winning name on the master list with an "Already Won" entry, so we can just select again.
Is it possible to add horizontal lines to a user form, like as shown in the attached image. I know about frames, but unsure how to get just one horizontal line.
i have faced a problem i want to draw straight connector using code imagine when you select straight connector to draw line mouse cursor changes when you move it to a node i want to do this with excel vba code without going to shapes and selecting connector.
I have this Command button that switches on and draws a line. If you press it again then I need to delete that line.
If I push the button multiple times Excel keeps changing the name of the line. I have multiple lines on the same worksheet and text boxes but only this 1 line needs to be delete. I would think that theres a way to assign a permanent name to this line and delete and recreate it but...
I have a 5-column listbox in a userform. I do not know how to make the column borders visible. If somebody can tell me, that would be great.
If not, then the next alternative would be to manually draw a line. I tried to so one, by creating a Label with a width of 1, and backcolor ot black. This works but not in front of a list box. For some reasons it cannot be brought to front/forward (order) of a listbox, I already right-clicked several times and sent tthe order to front.
I have attached a very simple example of what I am trying to achieve.So we have 2 sheets. a sheet called data and a sheet called results.
the data sheet, includes ID's of employees and what they are measured on - Hours and Accounts Finished.For example, worker 123 put in 100 hours from the 200 expected and finished 100 accounts from the 200 expected. The main sheet called Results looks up in a list every ID and displays in a table the compensation the worker got. Each time you select from the drop list a different ID number it shows the result and the amount of money they got.
I want to be able to draw every worker and display the compensation they got('Results', H11) in the Pay amount column('Data',H3:H11).
I have a worksheet set up as a draw sheet for a tournament. I have a set of rankings showing all points collated from last tournament. What i want to do is set up a formula/macro so that a cell containing a player eg. the winner will be found on the ranking list and the points won will be added to the next cell on the ranking list.
I've recently come across this wonderful site and have found it to be a pool for great information. I looking for some assistance with finding a macro that will draw circles/dots on a worksheet when the left mouse button is clicked at the mouse cursor location. There may be something out there already??? Here's exactly what I need; I want a macro that will draw 7 different coloured circles (blue, pink, cyan, lime green, red, yellow, and orange) on a worksheet. The macro would be activated from a button (7 different buttons- one for each colour) on the worksheet and when the left mouse button is clicked the coloured circle is drawn at the mouse cursor location.
I'm running a Secret Santa DJ competition this year and today now wish to make the draw.
Basically all entrants send in their mixes (in this case - upload to a webserver) and I then wish to try and use Excel to make the draw.
What I'd like to do is have a list of all the entrants in column a, use RAND() in column b to assign them each a number, and in column c use the list of entrants in column a to assign each person a mix to receive.
Through my browsing and searching on here, I came across the following (entered as an array); it's brilliant but unfortunately gives me duplications.
I have to draw a polygon of n sides whose , whose length of side will be given in the cells of excel and the diagram should be displayed in excel itself automatically
Like I have A1=5 A2=5 B1=4 B2=3 C4=5
Then diagram should be like in the attachment : Untitled5.png‎
I'm trying to get a random prize draw working with in excel. My worksheet structure as follows.
A1 person's name B2 points obtain by the person.
A1 B2 John 10 points Ken 20 points Amy 30 points
1st draw. If random number result between 1 and 10 display John wins then delete John's entries.
2nd draw etc... and delete anyone that has already won a prize.
With the following code I found on this forum it partly works. It only randomly select a cell but missing displaying the winner and removing it afterwards.
I have some code, in one part of it I am storing a number (between 1 and 8) as Integer. Is it possible to reference this variable to draw some rectangles on a sheet, sort of
"If MonitorDetail (this is the name of the variable) .value = 2 then
Here I place the code to draw the rectangle on the page. The macro currently stops at Monitordetail with a error telling me its a "Invalid Qualifier".
Currently we have data that is plotted to a scatter plot along with an already defined line. Next, with a straight edge we attempt to draw a line parallel to the already defined line through the scatter points of data. We basically find two points that give us the best fit to the original line. What I attempted to do was calcualte the slope of the original line and then the slope of each combination of two points in the data set. I then subtracted the slopes, found the minimum difference and thought that would give me the line most parallel to the original. This doesn't seem to work. I'm at a loss, any advice?
I want to put a length in one cell and a width in another. Then have excel draw a square/ rectangle to this size. It could just outline cells, each cell being one inch or any other way.
I came across an issue on the pivot table after refreshing data. I always need to manually redo the border and formatting. I figure that it is because every time when some new group have move to another day, it change the pivot table layout again and so on.
1) I manage to draw border for Day 1, 2, 3, 4 and 5 but 'Beyond Day 5' i dont know how to do it. 2) Sometime some Day X will have no data then i will have problem with my script. (example pivot table doesnt show Day 2) 3) Possible to do looping for that?
I am trying to automate drawing a flowchart based on a moel which the user can add or remove nodes and links from at will.
I can add shapes and position them but what I want to do is to insert connectors between existing shapes (I can draw two shapes and a connector at the same time but what I want to do is add a new node and then work out which existing nodes it links to and draw the lines)
Code: Function AddConnectorBetweenShapes(ConnectorType As MsoConnectorType, _ oBeginShape As Shape, oEndShape As Shape) As Shape 'The ConnectorType can be one of three constants - msoConnectorCurve, msoConnectorElbow, or msoConnectorStraight.
