I need help calculating max drawdown.

here is my data:

6,332 6,264 6,196 6,129 6,062 5,997 5,932 5,867 5,804 5,741 5,679 5,617 5,557 5,498 5,439 5,381 5,323 5,265 5,208 5,151 5,096

This data is in a row. I am running monte calro analysis so these numbers will change... And I am keeping track of the max of each iteration.

I don't have a lot of experience with Visual Basic. I did some work with VBA and VB.6 six or so years ago and I've recently had to do some VB.NET work on a project. .NET is definitely different than old VB and VBA. It's OO but its also just become more of a standard programming language. If you know basic programming in any language you can do basic programming in VB (although it certainly has some weird quirks).

My impression is that you should still use VBA for macro type tasks in Excel/Word/Whatever and you should use VB.NET (or better yet C#) if you actually want to make an application. For example if you want to do a lot of really complex processing on Excel documents I'd be inclined to write an actual .NET application that makes calls to Excel instead of writing a ton of VBA macros.

I would agree, although I'm certainly not very knowledgable on the subject. VBA is really great for some tasks and given that the majority of its audience isn't serious programmers I would think there's a significant obstacle to removing VBA and replacing it with something else.

I'm not sure what you mean. .NET is basically just a framework for running code and a collection of libraries that you can use in your code. So yes, you can write standalone applications. I would assume that you can also have a button in Excel that will run a .NET application.

I'm not entirely sure if you need an IDE - but its certainly helpful and Visual Studio is pretty good.

yeah i think here are some certs:

http://www.microsoft.com/learning/en...certifications

but from my googling most ppl think its pretty useless.

I agree it probably works fine with Pivot tables but I'm even worse than zomg as it comes to pivot tables; Ive never even used them

By the sound of his post I thought his data was just long columns with an entry on each new row...it's fine to sort this by Autofilter or just Sort.

that was my understanding as well...1 row per employee per swipe over a period of time. so what column are you going to sort on to show which employee has swiped the most over that period of time?


you need to get the data into an aggregated form where there's a single row for each employee (along with the sum of swipes) before you can sort the data.

idk...if you are going for a fin-analyst position, it may give you a slight leg up. we have fin-analysts that can refresh a ****** pivot table which is just asinine imo.

You're totally right!
Sorry

I made a sheet to keep track of some fantasy football stuff. Basically, what I want to be able to do is enter each persons score in one column for a week, have excel automatically sort those in descending order and then assign a respective value from 11 to 0 based on that order, and then sum those value across the row for each player.

Any way to do this?

Sorry all,

I've been travelling for the last couple of weeks and haven't had time to check the thread will go back and answer as much as I can

I assume you are talking about roto scoring, the way i did this in my fantasy sheet was i copy and pasted the roto table from the web then i ran a macro to automatically format it and sort but its hard to say without seeing all the data.

If you want to automatically sort you would have to do something in the vba (i posted a sort much earlier) but here would be the code to give a score:

if you wanted to account for 2 ppl tying and give half scores:

Without vba, no. Especially because you have Exam then 1,2,3,4 on different lines and i suspect you would want something that can dynamically create the worksheets if you add more exams

with that said heres something i threw together in 5 min

I have no idea what max drawdown is, if you are looking for the largest number you can use =MAX()

does this help you:

http://bytes.com/topic/access/answer...n-excel-2003-a

Exactly what I needed to get started, thanks. Figured out the rest on my own.

No worries on the delay--this has been on my mind for years.

So if I mess around with that snippet I can make a button or something and it will do it?

Thanks!
Buzz

Do you have iteration turned on or are you doing this in VBA?
Are you looking to track the max decrease of that series?

If you are doing Monte Carlo, I'd def recommend you do it in VBA.

heh, just had a job interview a few hours ago on the phone and was asked if I knew how to do pivot tables on excel. I had never heard of pivot tables (never had a real job before, never came up in school) and had to have him repeat himself a few times while I googled it (thank god for phone interviews) and then was like 'oh PIVOT tables, no I've never done that with excel but I'm sure it'd be easy to figure out'

then a few hours later found this thread

lesson learned: read more oot

what kinda job?

Funkii getting a job?

Pivot tables make this incredibly easy. (instructions for excel 2007)
Format your data like above, but instead of having exam a row above the number name them Exam 1, Exam 2, Lab 1 etc.
In the first column name the columm Name - include possible as a name here.
Select the whole table, and go Insert menu then Pivot table, just click OK to put it on a new worksheet.

On a new tab you will see a Pivot table appear with the field list on the right.
Drag the Name field to the Column Labels box
From the drop down on the pivto table now, select Bob Barker (or whomever) AND Possible - right click the Grand Total box and select Remove Grand Total.
If Possible isnt on the right most column, right click that and select Move -> Move possible to the end
Pull in the Exam and labs to the Values box (or you can just click off the check box)
In the Column labes box, pull the Values selection to the Row Labels box.

You're done.

You can rename the Sum of Exam 1 or whatever to Exam 1 if you wish.
One advantage here is you can just click on Column Labels dropdown to select any name you want to change this report.

If you add in more Labs or Exams (or people) (they don't need to be in order on the data sheet you can just reorder them on the pivot), make sure you increase the pivot source area to include them. To do this - click on the pivot and then click options under the pivot table tools and the select change data source and reselect the data for the table. The pivot will refresh.

This is a VERY basic use of pivot tables, and the beneft becomes more and more obvious with more detailed sets of data where you can drill down, summerize by various ways, show values as % etc...

I despise Pivot Tables - they are OK for one-ime calculations but for repeated use they are terrible. I try to use SUMIF and arrays to the extent possible to avoid using Pivot Tables.

Can you give a reason why? I use them constantly for my work. Can you create anything else that would say take a list of 100k items, each with a final value type column and say 60+ attributes with various number of possible values for those attributes and slice the data anyway you vritually instantly with no need for additional calc'ed fields or codes?

Real life example - trade volume for a month (say 125k trades) - including trade type,trading desk, subtype, customer type, customer name, notional, short/long, region etc. etc. etc.

Select the full table and pivot.

I can now list trade count per customer and notional per customer for analysis... whats that? you only want internal customers in asia? literally less than 5 seconds to change the filters. Oh divide it up by buy vs sell? adding in column - 1 second. Oh by trading desk and not customer- 1 second to change to that. Oh not by notional? Rather as a % of the total? Change the value display type.

Once you have the data to hand, it allows you to query very quickly.

Let's say I have a set of names associated with cells (using the formula tools), and would like to use those to de-reference cells through an external application. to do this, I want to keep a list of defined names in the worksheet itself so that the external app. can open the sheet, see what is available, and bind the names to cells for internal use (almost an API).

Is there a way to automatically keep a current list of defined names on a separate sheet? I.e. I can paste the current set manually using the formulas->"use in formulas"->"paste names", but would like this list to just update itself when I add or remove name definitions. Is this possible?

many thanks if anyone knows this, I just started playing with this yesterday and hate/don't trust anything that requires manual maintenance.

can't think of a way without writing a macro to iterate through the range collection in vba.

cool, I think I can call vba type functions through the tunnel when I open the sheet; so I could probably just do this first. I think this would either work, or is enough to get me oriented. thanks!

Something like: