Two Plus Two Publishing LLC
Two Plus Two Publishing LLC
 

Go Back   Two Plus Two Poker Forums > >

Notices

Other Other Topics Discussion of arts & entertainment, pop culture, food & drink, health and exercise, fashion, relationships, work, and just about anything else in life except poker, sports, religion and politics.

Reply
 
Thread Tools Display Modes
Old 09-25-2010, 05:12 PM   #151
ryanthe4aces
enthusiast
 
Join Date: Oct 2008
Posts: 95
Re: Ask me anything about Microsoft Excel

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.
ryanthe4aces is offline   Reply With Quote
Old 09-25-2010, 05:42 PM   #152
jjshabado
Carpal Tunnel
 
jjshabado's Avatar
 
Join Date: Jul 2006
Posts: 22,164
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
It seems like there is a belief that microsoft is trying to phase out VBA in favour of vb.net, are you a .net programmer? any comments on advantages? switching process? I think i remember reading that it is OO, I have some history with java so i imagine the switch, at least on a conceptual level wouldn't be too difficult.
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.

Quote:
Originally Posted by zomg View Post
Even if they do phase out VBA in office 2012+ (conservative guess, according to a quick search it looks like they have removed it from autoCAD 2010) at the current rate of adoption I can't really seeing it being a huge issue for the next 5 years.
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.

Quote:
Originally Posted by zomg View Post
How does it run? Are they standalone applications? Can i put a button in excel that will run a .NET script? I assume you need an IDE (visual studio?) I'll probably download it in the next couple of weeks when I have some time.
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.
jjshabado is offline   Reply With Quote
Old 09-26-2010, 02:24 AM   #153
john voight
Carpal \'Tunnel
 
john voight's Avatar
 
Join Date: Apr 2006
Posts: 13,540
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by PolvoPelusa View Post
they have a cert for excel?
yeah i think here are some certs:

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

but from my googling most ppl think its pretty useless.
john voight is offline   Reply With Quote
Old 09-26-2010, 04:34 AM   #154
Malfunction
old hand
 
Join Date: Jul 2008
Location: Limping sooted one-gappers
Posts: 1,719
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by PolvoPelusa View Post
this won't work. you need to aggregate the data first...perfect opportunity for a pivot table.


Select the data range and create a pivot table. Lets assume its simply 2 columns: employee and swipe-timestamp. Add [employee] to the row labels group and the [swipe-timestamp] to the values group. Now just sort the employee row label by [swipe-timestamp] ascending.
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.
Malfunction is offline   Reply With Quote
Old 09-26-2010, 12:44 PM   #155
PolvoPelusa
adept
 
Join Date: Aug 2008
Location: Century Laboratories
Posts: 974
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by Malfunction View Post
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.
PolvoPelusa is offline   Reply With Quote
Old 09-26-2010, 12:47 PM   #156
PolvoPelusa
adept
 
Join Date: Aug 2008
Location: Century Laboratories
Posts: 974
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by john voight View Post
yeah i think here are some certs:

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

but from my googling most ppl think its pretty useless.


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.
PolvoPelusa is offline   Reply With Quote
Old 09-26-2010, 01:16 PM   #157
Malfunction
old hand
 
Join Date: Jul 2008
Location: Limping sooted one-gappers
Posts: 1,719
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by PolvoPelusa View Post
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.
You're totally right!
Sorry
Malfunction is offline   Reply With Quote
Old 09-28-2010, 04:47 PM   #158
dkgojackets
DKGOAT
 
dkgojackets's Avatar
 
Join Date: May 2006
Posts: 83,048
Re: Ask me anything about Microsoft Excel

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?
dkgojackets is offline   Reply With Quote
Old 09-28-2010, 05:32 PM   #159
zomg
Excelling at Flash
 
zomg's Avatar
 
Join Date: Nov 2006
Posts: 1,488
Re: Ask me anything about Microsoft Excel

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
zomg is offline   Reply With Quote
Old 09-28-2010, 05:41 PM   #160
zomg
Excelling at Flash
 
zomg's Avatar
 
Join Date: Nov 2006
Posts: 1,488
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by dkgojackets View Post
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?
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:

Code:
=12-RANK(B1,B$1:B$10)
if you wanted to account for 2 ppl tying and give half scores:
Code:
=IF(COUNTIF(B$1:B$10,B1)>1,11.5-RANK(B1,B$1:B$10),12-RANK(B1,B$1:B$10))
zomg is offline   Reply With Quote
Old 09-28-2010, 05:56 PM   #161
zomg
Excelling at Flash
 
zomg's Avatar
 
Join Date: Nov 2006
Posts: 1,488
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by Buzz-cp View Post
I use Excel for grading. I'd like to prepare progress reports that I could print out.

I have grade categories and assignments in columns and names/IDs in rows along with points for each. Something like this:

Code:
            Exam                Lab
Name          1   2   3   4      1   2   3   4  
 Possible -> 100 100 100 100     25  25  25  25
Bob Barker    95  89  75  79     24  15  23  0
Jens Kruger   88  59  65  47     19  17  20  21
So we make a report for Bob Barker on a new worksheet, and it looks like this:

Code:
Name: Bob Barker
Date: 1/1/2010
           Possible  Score
Exam 1        100      95
Exam 2        100      89
Exam 3        100      75
Exam 4        100      79
Lab 1          25      24
Lab 2          25      15
..etc
Any way to do this without much VBA? I can muck VBA and figure out how to edit existing scripts, but haven't really learned it.
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

Code:
Sub createIndividualReport()
    Dim dataSht As Worksheet
    Dim tmpSht As Worksheet
    
    Set dataSht = ThisWorkbook.Sheets("Data")
    
    For i = 4 To dataSht.Range("A65000").End(xlUp).Row
        Set tmpSht = Sheets.Add
        tmpSht.Name = dataSht.Range("A" & i)
        tmpSht.Range("A1") = dataSht.Range("A" & i)
        tmpSht.Range("A2") = Format(Now, "mm/dd/yyyy")
        tmpSht.Range("B3") = "Possible"
        tmpSht.Range("C3") = "Score"
        
        curRow = 4
        For x = 2 To dataSht.Range("IV2").End(xlToLeft).Column
            If dataSht.Cells(1, x) <> "" Then curCat = dataSht.Cells(1, x)
            tmpSht.Range("A" & curRow) = curCat & " " & dataSht.Cells(2, x)
            tmpSht.Range("B" & curRow) = dataSht.Cells(3, x)
            tmpSht.Range("C" & curRow) = dataSht.Cells(i, x)
            curRow = curRow + 1
        Next x
    Next i

End Sub
zomg is offline   Reply With Quote
Old 09-28-2010, 06:02 PM   #162
zomg
Excelling at Flash
 
zomg's Avatar
 
Join Date: Nov 2006
Posts: 1,488
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by ryanthe4aces View Post
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 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
zomg is offline   Reply With Quote
Old 09-28-2010, 08:31 PM   #163
dkgojackets
DKGOAT
 
dkgojackets's Avatar
 
Join Date: May 2006
Posts: 83,048
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
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:

Code:
=12-RANK(B1,B$1:B$10)
if you wanted to account for 2 ppl tying and give half scores:
Code:
=IF(COUNTIF(B$1:B$10,B1)>1,11.5-RANK(B1,B$1:B$10),12-RANK(B1,B$1:B$10))
Exactly what I needed to get started, thanks. Figured out the rest on my own.
dkgojackets is offline   Reply With Quote
Old 09-28-2010, 09:46 PM   #164
Buzz-cp
Carpal \'Tunnel
 
Buzz-cp's Avatar
 
Join Date: Sep 2005
Location: :noitacoL
Posts: 10,902
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
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
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
Buzz-cp is offline   Reply With Quote
Old 09-28-2010, 11:44 PM   #165
PayTheSnucka
grinder
 
PayTheSnucka's Avatar
 
Join Date: Jul 2004
Posts: 564
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by ryanthe4aces View Post
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.
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.
PayTheSnucka is offline   Reply With Quote
Old 09-29-2010, 01:10 AM   #166
NHFunkii
Carpal \'Tunnel
 
NHFunkii's Avatar
 
Join Date: Jul 2005
Location: is this real life?
Posts: 14,641
Re: Ask me anything about Microsoft Excel

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
NHFunkii is offline   Reply With Quote
Old 09-29-2010, 07:37 AM   #167
PolvoPelusa
adept
 
Join Date: Aug 2008
Location: Century Laboratories
Posts: 974
Re: Ask me anything about Microsoft Excel

what kinda job?
PolvoPelusa is offline   Reply With Quote
Old 09-29-2010, 08:43 AM   #168
JL514
because i can
 
JL514's Avatar
 
Join Date: Nov 2006
Posts: 9,545
Re: Ask me anything about Microsoft Excel

Funkii getting a job?
JL514 is offline   Reply With Quote
Old 09-29-2010, 02:45 PM   #169
Hellrazor
grinder
 
Join Date: Jul 2003
Location: 'Jersey
Posts: 640
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by Buzz-cp View Post
I use Excel for grading. I'd like to prepare progress reports that I could print out.

I have grade categories and assignments in columns and names/IDs in rows along with points for each. Something like this:

Code:
            Exam                Lab
Name          1   2   3   4      1   2   3   4  
 Possible -> 100 100 100 100     25  25  25  25
Bob Barker    95  89  75  79     24  15  23  0
Jens Kruger   88  59  65  47     19  17  20  21
So we make a report for Bob Barker on a new worksheet, and it looks like this:

Code:
Name: Bob Barker
Date: 1/1/2010
           Possible  Score
Exam 1        100      95
Exam 2        100      89
Exam 3        100      75
Exam 4        100      79
Lab 1          25      24
Lab 2          25      15
..etc
Any way to do this without much VBA? I can muck VBA and figure out how to edit existing scripts, but haven't really learned it.

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...
Hellrazor is offline   Reply With Quote
Old 09-29-2010, 06:30 PM   #170
PayTheSnucka
grinder
 
PayTheSnucka's Avatar
 
Join Date: Jul 2004
Posts: 564
Re: Ask me anything about Microsoft Excel

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.
PayTheSnucka is offline   Reply With Quote
Old 09-30-2010, 06:03 AM   #171
Hellrazor
grinder
 
Join Date: Jul 2003
Location: 'Jersey
Posts: 640
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by PayTheSnucka View Post
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.
Hellrazor is offline   Reply With Quote
Old 09-30-2010, 11:05 AM   #172
swiz
enthusiast
 
Join Date: Dec 2007
Posts: 61
Re: Ask me anything about Microsoft Excel

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.
swiz is offline   Reply With Quote
Old 09-30-2010, 11:32 AM   #173
PolvoPelusa
adept
 
Join Date: Aug 2008
Location: Century Laboratories
Posts: 974
Re: Ask me anything about Microsoft Excel

can't think of a way without writing a macro to iterate through the range collection in vba.
PolvoPelusa is offline   Reply With Quote
Old 09-30-2010, 12:07 PM   #174
swiz
enthusiast
 
Join Date: Dec 2007
Posts: 61
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by PolvoPelusa View Post
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!
swiz is offline   Reply With Quote
Old 09-30-2010, 12:36 PM   #175
zomg
Excelling at Flash
 
zomg's Avatar
 
Join Date: Nov 2006
Posts: 1,488
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by swiz View Post
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:

Code:
Private Sub Worksheet_Activate()
ActiveSheet.Columns("A:A").ClearContents
cRow = 1
For Each nm In ThisWorkbook.Names
    ActiveSheet.Range("A" & cRow) = nm.Name
    cRow = cRow + 1
Next nm
End Sub
zomg is offline   Reply With Quote

Reply
      

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Forum Jump


All times are GMT -4. The time now is 06:13 AM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright ę 2008-2017, Two Plus Two Interactive
 
 
Poker Players - Streaming Live Online