Formula to populate Line Chart & Data Automatically? - Video explaining inside!

[domgilberto]

Hey All!


Would really apreciate anyones help on solving a pickle for me within excel - To make this really clear and punchy for the helper, I've created a video talking through the already made spreadsheet and the requirements I am looking for!

Any questions feel free to ask on here!

Video: http://www.screencast.com/t/64qHBlJ3

It's just a few minutes long and should hopefully explain everything I require!

I will be incredibly thankful for anyone's time if they're kind enough to share a few minutes!


Thanks in advance!!

Dominic.


(excel spreadsheet is also attached!)

[domgilberto]

---bump---

[domgilberto]

Any clever people out there ...

[Pete_UK]

Nice video, but there is a lot of detail in there that one can't take in all at once, and I don't fancy viewing it several times to take notes. Can't you just summarise your requirements in writing? It might be better to break it down into small items that you want doing, rather than ask for the complete finished article.

Are you aware of the INDIRECT function? It strikes me that you can make use of that to adjust your ranges from the value in D4. I can't remember all the other things you were asking about towards the end of the video.

Hope this helps.

Pete

[domgilberto]

Hey Pete,

Thanks for spending your time with me, again!


So basically this is a list of what I require:

1) I place number of trades I desire at the top of "Graph" tab
2) This will divide it by 12 months of the year
3) the data from outcome will be pulled into the line charts and tables below - this, of course, corresponding to number of trades per year, month etc.
4) on the "Outcome" tab, what was the number of LOSS and WIN streaks consecutively I experienced over that interim - I.e. Month 1 - Month 2 - Year etc...
5) What was the lowest the balance when to on the given interim (i.e. Month 1 - Month 2 etc...


Let me know if this makes sense? I am pretty useless with Excel so you'll have to bare with me and the specific technical terms of what I require? (as in, I do not understand INDIRECT function... etc....)

[Pete_UK]

Okay, to get you started I would suggest that you put this formula in D4 of the Graph sheet:

Please Login or Register  to view this content.

to ensure that it is an integer. Then in D29 you could have this formula:

Please Login or Register  to view this content.

and this one in M29:

Please Login or Register  to view this content.

Now, if you change the value in D3 to say 360, then those two Account Balances will also change automatically.

I'm not sure what the relationship is between D4 and your yearly graphs, as you didn't include those in the video (maybe you did, but I've forgotten), but hopefully you can see how it could be included within an appropriate formula.

You will need to do a similar thing for the source range for the graphs, but you might have to set up dynamic named ranges and then refer to the name within the graphs. I don't have time to look at this at the moment.

Hope this helps.

Pete

[domgilberto]

Damn I wish I had your excel knowledge! I cant thank you enough - Too kind.


You've nailed the first parts spot on.

No worries if you dont have time now, but if you managed to continue being a good man (and find some time when you're free?) hopefully this will shed some light about the rest of the chart:

First Month Graph:

Account balance: This is done perfectly, as per your formula above.
Peak Drawdown: This just takes the data range from OUTCOME 3 (the same data that account balance figure is populated from - based upon "Number of Trades" at the top) and shows me what the lowest the balance went to over that period of trades
Losing Streak: Shows me what number of "LOSS", consecutively I had over that month of trades ("Number of Trades" / 12 = "Average Per Month" = 10 e.g.) - (which is, in this example = 2 over 10 trades)
Winning Streak: The same as above but the consecutive WIN over the same period of time / or trades....


"Second Month" is just the "First Month"s formula doubled... I.e. the number of "trades" on OUTCOME 3; it's just looking at 20 trades (120/12 = 10*2 (months) = 20)

Year 1 = Entire year worth of data = 120 trades
Year 2 = All data over 2 years = 120*2(year) = 240 trades (again, from OUTCOME 3)

And so on....


I hope this helps? Again, thanks for the time you've spent in helping a stranger! Too kind :D

[domgilberto]

---Bump---

(forgive me if this is coming across as inpatient. Just want to keep this alive that's all!

[Pete_UK]

Hi Dominic,

no-one else seems to want to help you !!

For the Peak draw-down I think you just want the minimum of the range in the cell above, so you could put this in D30:

=MIN(INDIRECT("'OUTCOME 3'!A2:A"&$D$4))

and this in M30:

=MIN(INDIRECT("'OUTCOME 3'!A2:A" & 2*$D$4))

with similar approaches for the other yearly periods.

It would be useful in any follow-up posts to explain where you are up to now, so did you apply the formulae to get the Account Balances for the yearly periods? Have you adjusted the source data for the graphs? Have you now got the Peak draw-down for all the periods? What's left to do?

Hope this helps.

Pete

[domgilberto]

I know! Just you


I have made a quick video about "drawdown" - I wasnt too clear on the original request!

Video: http://www.screencast.com/t/l8VePv655c



Let me know if this is clear... You'll be glad to know that I've cheekily asked for one last favour (on top of the multiple ones already!!) :P


Thanks Pete!!


Update - Excel spreadsheet attached with new part.

Break-down of things required; things done:

1) Graph 3 - "Number of Trades (Year)" - "Average Per Month" = Complete
2) Account Balance connected with number 1 = Complete
3) Peak Drawdown (explained in video) = Need Formula
4) Losing Streak = Need Formula
5) Winning Streak = Need Formula
6) Line Charts to be updated in conjunction with number 1 = Need Formula
7) "Monthly" Tab (attached in new spreadsheet here) and the corresponding months in relation to "Average Per Month" figure (as mentioned in video) = Need Formula.


I hope this helps you help me! :D

[domgilberto]

----bump----

[domgilberto]

----bump----

[domgilberto]

Does anyone at least know how to create the formula for the following:

1) Peak drawdown - The percentage change from the highest the "Account Balance" went to... to the lowest the account balance went to in the given number of trades - 1 month, year 1, year 2 etc...
2) Win losses consective...


Would really appreciate just these two and that's pretty much it ...

[domgilberto]

----bump----

[domgilberto]

----bump----

[domgilberto]

anyone there...