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-12-2010, 05:54 PM   #51
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 PolvoPelusa View Post
I do the exact same thing for a large company in the US (although we funnel everything into a bunch of SQL Server databases via SSIS and just use Excel/Access for UI. I love the company I work for and don't want to go full-time contract, but picking up some extra dough on the side (especially at $50-90 /hr) doesn't sound bad at all.

Could you elaborate on how you find legit projects on Craigslist for that price range?
The job i got off craigslist i would say was just lucky, I moved here in december last year and travelled the states on and off for about 6 months, spending my savings and not doing much work at all. Then one day i saw an ad for access/excel vba and i sent them an email the next day i made my only visit to their office and i have been working for them since. Every other contract I have, i worked for them at one stage (except my latest one which was through a friend)

This job also started at $30 p/h for the first month because they wanted me to do work in access/mssql/php/mysql which were all languages i had never used before. It has since gone up to $50 and is still my lowest rate but they give me the most work so i'm not complaining.

It seems like you have a lot of other skills, and I would suggest maybe learning some PHP and getting into contract web design, theres lots of work out there for a good php/mysql programmer. PHP is an awesome language i had experience with HTML/CSS/Javascript and was fairly competent in a month. The best thing about this industry is i never get stuck doing the same thing for too long, there's always another language to learn or a different project to do.

I understand people who program 9-5 for the security but I can think of nothing more tedious than working on the same program for years at a time. I'm fortunate that i'm still young and selfish and don't have anyone else relying on me to make money, it's good while it lasts.

As an aside this attitude does create an interesting dynamic with my 'clients' who all know that work is far from my main priority and that i'm not in it for the long haul. In my interview with the company from craigslist i flat out told them that while I was happy to do the occasional all nighter, I didn't come to NYC to work 12 hours days and I plan on travelling a lot.
zomg is offline   Reply With Quote
Old 09-12-2010, 06:02 PM   #52
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 PolvoPelusa View Post
I doubt I'm "further down the road" than you, but my plan is to attack some of the project manager type roles at my current company as soon as I absorb a bit more of the business-knowledge (supply-chain) and people management skills. Simply put, I want to be the link between senior management and a team of my clones. Currently (at the company I work for), there are only a handful of these type of people (that I'm aware of) and they are treated like gold.
Part of the reason i moved was that i wasn't ready to get a 9-5 job back home because I didn't know where i wanted to be. I think the best use for my skills would be external to a company if only because i get bored easily and i wouldn't last at a single company long enough to move up the ladder. My hate for the corporate ladder is another topic

Quote:
Originally Posted by CopTHIS View Post
Thanks, I'll fiddle about with coding when I'm next at work. I just find it fiddly - eg I'm sure that I've been able to use the range command when the variable is the row but not when the variable is the column. I think range("A" & var : "C" & var2) worked but range(var & "1" : var2 & "2") or something similar wouldn't - if you see what I mean.
Yes, i just looked at the code and it won't actually work, you can only use range(var & "1" : var2 & "2") if var2 is a letter (you could use ASC() ) and convert it to a letter but it's not a good way.

This is the only time i use the .cells command

e.g.

range(cells(1,var1),cells(2,var2))

hope that makes sense

edit: if var1 and var2 are letters then the correct syntax would be:

range(var1 & "1:" & var2 & "2")

note that you need the colon inside the quotes

Quote:
Originally Posted by jjshabado View Post
What do you mean by this? What kind of things do you do?

I can't imagine a whole lot of things where Excel is the right tool for the job for back end work. At the bare minimum I could see writing a simple script to import the Excel data into Access for proper processing/querying/reporting.

Edit: Actually I think its super easy to import Excel data into Access using VB - but it's been a long time since I've done Excel/Access programming.
Templates used company wide, write a macro to go through every file in a folder and aggregate in a spreadsheet, i mentioned this before but most companies don't want to use access and are happy living their lives in excel. A lot of the time I will be doing things where the company has an existing process with a designated input and output that they are happy with. For example a movie studio had a list of all DVD release dates in a master file with data spread across 4 different tabs, when ever they wanted to make a monthly one someone would spend hours copy and pasting from all the different sheets and creating a monthly workbook. The workbook design was already decided on and everyone was happy with it, all i did was automate the creation of it.

Sure it might be more efficient to move all the DVD releases to a huge database but that would require a huge change which no one wants to pay for.

This is especially true with anyone in the finance industry who would never leave excel if they didnt have to.

edit: i see copThis covered this, this is the attitude of every company i have worked with

Last edited by zomg; 09-12-2010 at 06:17 PM.
zomg is offline   Reply With Quote
Old 09-12-2010, 06:06 PM   #53
Hawk35
old hand
 
Hawk35's Avatar
 
Join Date: Nov 2006
Posts: 1,449
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by Big TR View Post
Sorry, but I'll be a dick and mention that an ask me thread about Excel in which the OP can't discuss the use of pivot tables is laughable.
This
Hawk35 is offline   Reply With Quote
Old 09-12-2010, 08:53 PM   #54
jjshabado
Carpal Tunnel
 
jjshabado's Avatar
 
Join Date: Jul 2006
Posts: 22,383
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by CopTHIS View Post
fwiw I work as an actuary and we use spreadsheets for calcs and modelling all the time. For big jobs involving data we may use Access, and some specific jobs have their own software, but we are not programmers and Excel is so easy to use and ideal for what we do.
Sure this makes sense - and its a good reason to use Excel. But once you get to the point where you have a job that is too complicated to do in-house and you get an outside contractor to work on the problem it seems strange that you still get a basic product.
jjshabado is offline   Reply With Quote
Old 09-12-2010, 08:58 PM   #55
jjshabado
Carpal Tunnel
 
jjshabado's Avatar
 
Join Date: Jul 2006
Posts: 22,383
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
Templates used company wide, write a macro to go through every file in a folder and aggregate in a spreadsheet, i mentioned this before but most companies don't want to use access and are happy living their lives in excel. A lot of the time I will be doing things where the company has an existing process with a designated input and output that they are happy with. For example a movie studio had a list of all DVD release dates in a master file with data spread across 4 different tabs, when ever they wanted to make a monthly one someone would spend hours copy and pasting from all the different sheets and creating a monthly workbook. The workbook design was already decided on and everyone was happy with it, all i did was automate the creation of it.

Sure it might be more efficient to move all the DVD releases to a huge database but that would require a huge change which no one wants to pay for.
Fair enough. I would still think its better to take the raw data, import it into Access, and then show people how they can do all sorts of stuff (ad-hoc querying, create standard reports, what have you). I mean we're talking about at most a couple of days of work and a super easy product to work with. It becomes much easier to use.

Quote:
Originally Posted by zomg View Post
This is especially true with anyone in the finance industry who would never leave excel if they didnt have to.

edit: i see copThis covered this, this is the attitude of every company i have worked with
So you spend a lot of time doing really small jobs like this? Each of your examples seems like only a couple of hours of work. Are you just contracted as this companies Excel dude and given random tasks?
jjshabado is offline   Reply With Quote
Old 09-12-2010, 08:59 PM   #56
growingskin
newbie
 
Join Date: Sep 2010
Posts: 45
Re: Ask me anything about Microsoft Excel

I want to make my row change it's color (green) automatically when I enter a text in one column of the row like "paid", how do you do that?
growingskin is offline   Reply With Quote
Old 09-12-2010, 09:51 PM   #57
econophile
Carpal \'Tunnel
 
econophile's Avatar
 
Join Date: Jul 2005
Location: (X'X)^(-1)X'Y
Posts: 11,193
Re: Ask me anything about Microsoft Excel

conditional formatting
econophile is offline   Reply With Quote
Old 09-12-2010, 10:50 PM   #58
PolvoPelusa
adept
 
Join Date: Aug 2008
Location: Century Laboratories
Posts: 974
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by jjshabado View Post
Fair enough. I would still think its better to take the raw data, import it into Access, and then show people how they can do all sorts of stuff (ad-hoc querying, create standard reports, what have you).
who are these people you speak of that can be taught the things you list?

99% of my audience require their data to be spoon fed to them. I've realized that most people think that being able to put some numbers in a few cells of an excel spreadsheet means they know how to use excel...when in reality, it just means they can put some numbers in a few cells of an excel spreadsheet.

I'd rather bleed from my ears than waste my time trying to explain to them how to query data from a relation data set.
PolvoPelusa is offline   Reply With Quote
Old 09-12-2010, 11:04 PM   #59
Cueballmania
Carpal \'Tunnel
 
Cueballmania's Avatar
 
Join Date: Sep 2005
Location: Sioux Falls, SD
Posts: 14,161
Re: Ask me anything about Microsoft Excel

Okay, here is a logistical problem that I do not know if Excel can handle.

I have this algorithm based on the Colley's Matrix Rating System used in the BCS for college football. Basically, you set up a matrix and solve an eigenvalue equation. Here is my spreadsheet that I created implementing the algorithm, and it works fine for storing my matrix and calculating some trivial things.

Here is my file: matrixratings.xlsx

My question, is there a way to implement an automatic counter so all I have to do is input a winner and a loser (the opponent of the winner) and it will a) automatically decrement the two intersecting rows and columns and b) update the win/lose columns appropriately? Since I am human, there are times where I mess up and need to re-enter things from a previous save.

Also,is there a way to solve the eigenvalue equation (to the appropriate precision) inside of Excel? Right now I have to copy and paste the matrix values into a new page (The CVS page), export the page as a csv file, import into Mathematica, type in the appropriate commands and paste in the values into Excel.
Cueballmania is offline   Reply With Quote
Old 09-12-2010, 11:19 PM   #60
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 jjshabado View Post
Sure this makes sense - and its a good reason to use Excel. But once you get to the point where you have a job that is too complicated to do in-house and you get an outside contractor to work on the problem it seems strange that you still get a basic product.
Quote:
Originally Posted by jjshabado View Post
So you spend a lot of time doing really small jobs like this? Each of your examples seems like only a couple of hours of work. Are you just contracted as this companies Excel dude and given random tasks?
The answer to both of these are pretty similar in that while I was at university i worked part time for three different companies as part of my degree. While working there I did excel development for them on the side in my spare time. Through those contacts i got work doing small-ish jobs (usually 20-40 hours at a time).

In addition during my last year of university i worked at a huge fund management firm 3 days a week, they had about 1000 different spreadsheets used nationwide and i basically worked with every level of management trimming the fat and automating everything. They still have small things that they need and e-mail me occasionally.

My main source of income at the moment is for an american company that i do 20-40 hours p/w depending on what's going on in my life.

The only contract i have that falls outside of this is one my friend got me because he knows what i can do in excel and he saw where it could be used so he approached management, they asked for a sample that i did in a few hours for free and now have about 50 hours work lined up over the next 3 months at almost double what i'm getting paid in NY

Quote:
Originally Posted by jjshabado View Post
Fair enough. I would still think its better to take the raw data, import it into Access, and then show people how they can do all sorts of stuff (ad-hoc querying, create standard reports, what have you). I mean we're talking about at most a couple of days of work and a super easy product to work with. It becomes much easier to use.
Quote:
Originally Posted by PolvoPelusa View Post
who are these people you speak of that can be taught the things you list?
this. It's either trying to teach a whole company how to use a brand new database software or writing a macro to compile data at the end. A lot of my work is with the final user of the data when they dont have the funding/time to go through there main IT department and can't change the input. whacking a database on at the end is just asking to be not used as soon as i walk out the door.
zomg is offline   Reply With Quote
Old 09-12-2010, 11:21 PM   #61
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 growingskin View Post
I want to make my row change it's color (green) automatically when I enter a text in one column of the row like "paid", how do you do that?
In 2007: Click on the cell, go to the home tab on the ribbon, select conditional formatting > 'equal to' then in the box that comes up type "paid" (without quotes)

In 2003: from memory i think its data > conditional formatting then go to formula and enter ="Paid"
zomg is offline   Reply With Quote
Old 09-12-2010, 11:28 PM   #62
jjshabado
Carpal Tunnel
 
jjshabado's Avatar
 
Join Date: Jul 2006
Posts: 22,383
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by PolvoPelusa View Post
who are these people you speak of that can be taught the things you list?

99% of my audience require their data to be spoon fed to them. I've realized that most people think that being able to put some numbers in a few cells of an excel spreadsheet means they know how to use excel...when in reality, it just means they can put some numbers in a few cells of an excel spreadsheet.

I'd rather bleed from my ears than waste my time trying to explain to them how to query data from a relation data set.
Um... that's why you create reports for them. Access has lots of ways to view the data that requires no knowledge of databases. Other DBs have similar free reporting software.

My point is just that if people are this stupid it seems useful to make the data even simper than an excel spreadsheet.
jjshabado is offline   Reply With Quote
Old 09-12-2010, 11:42 PM   #63
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 Cueballmania View Post
Here is my file: matrixratings.xlsx

My question, is there a way to implement an automatic counter so all I have to do is input a winner and a loser (the opponent of the winner) and it will a) automatically decrement the two intersecting rows and columns and b) update the win/lose columns appropriately? Since I am human, there are times where I mess up and need to re-enter things from a previous save.
tbh the whole spreadsheet looks prone to errors, lets say Aves beats Brian then what value do you put in C6 and F3? how do you know who beat who? do you put a -1 then manually update the win/loss columns?
zomg is offline   Reply With Quote
Old 09-12-2010, 11:51 PM   #64
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 jjshabado View Post
Um... that's why you create reports for them. Access has lots of ways to view the data that requires no knowledge of databases. Other DBs have similar free reporting software.

My point is just that if people are this stupid it seems useful to make the data even simper than an excel spreadsheet.
Even in the companies that have databases, they all want their reports in excel because that's what everyone is used to. No one sees the value in retraining a few people on access for benefits that they can't see. I already said a few times I agree that using databases is the better solution most companies just don't care.
zomg is offline   Reply With Quote
Old 09-13-2010, 12:20 AM   #65
Cueballmania
Carpal \'Tunnel
 
Cueballmania's Avatar
 
Join Date: Sep 2005
Location: Sioux Falls, SD
Posts: 14,161
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
do you put a -1 then manually update the win/loss columns?
Yes.

Hence, why I'm asking if there is an easier way.
Cueballmania is offline   Reply With Quote
Old 09-13-2010, 01:20 AM   #66
zomg
Excelling at Flash
 
zomg's Avatar
 
Join Date: Nov 2006
Posts: 1,488
Re: Ask me anything about Microsoft Excel

I added another sheet called data then in column A i put the winner and column B the loser in the matrix cell B2 put the formula

=IF(B$1=$A2,$AG2+2,(SUM((data!$A$2:$A$1000=$A2)*(d ata!$B$2:$B$1000=B$1)*1)+SUM((data!$A$2:$A$1000=B$ 1)*(data!$B$2:$B$1000=$A2)*1)))

instead of pressing enter press ctrl+shift+enter (this does an array formula) then copy and paste the formula in B3:B29 then copy and paste B2:B29 to C2:AC2 (you cant copy and paste over an existing array formula so you have to paste around if that makes sense

this will complete the grid then in the win column put

=COUNTIF(data!A:A,A2)

and for losses put

=COUNTIF(data!B:B,A2)

I realise you will have to go back and add in the games but its probably better having them all written down so you can go back and verify results
zomg is offline   Reply With Quote
Old 09-13-2010, 02:07 AM   #67
Cueballmania
Carpal \'Tunnel
 
Cueballmania's Avatar
 
Join Date: Sep 2005
Location: Sioux Falls, SD
Posts: 14,161
Re: Ask me anything about Microsoft Excel

Cool, thanks. I'll try it. Is there a way to solve the eigenvalue equation inside Excel?
Cueballmania is offline   Reply With Quote
Old 09-13-2010, 04:54 AM   #68
CopTHIS
Pooh-Bah
 
Join Date: Jan 2006
Posts: 4,094
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by jjshabado View Post
Sure this makes sense - and its a good reason to use Excel. But once you get to the point where you have a job that is too complicated to do in-house and you get an outside contractor to work on the problem it seems strange that you still get a basic product.
We actually never use contractors - either we do whatever is needed ourselves, or use specific software that has been developed for that purpose. (There is really one one job that we use that software for.)

The thing about Excel is that you can basically use it as a calculator - in fact. some people in the team (me included) don't have a calculator at work even though we fiddle about with numbers all the time. It's so much easier in Excel as you can break the formula down, see intermediate steps etc. Most jobs are simple-ish and one offs, rather than projects so Excel is perfect. That said, there are some jobs that we've used Excel for that would have been better in Access.
CopTHIS is offline   Reply With Quote
Old 09-13-2010, 08:22 AM   #69
PolvoPelusa
adept
 
Join Date: Aug 2008
Location: Century Laboratories
Posts: 974
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by jjshabado View Post
Fair enough. I would still think its better to take the raw data, import it into Access, and then show people how they can do all sorts of stuff (ad-hoc querying, create standard reports, what have you).
Quote:
Originally Posted by jjshabado View Post
Um... that's why you create reports for them.

Quote:
Originally Posted by jjshabado View Post
My point is just that if people are this stupid it seems useful to make the data even simper than an excel spreadsheet.
simpler than an excel spreadsheet? example?

also, you'd be surprised how many people want to see all the "fancy colors". "Can we do that red, yellow, green thingy you did for the XYZ reporting?" What is simpler than excel and allows you to do "green thingys"?
PolvoPelusa is offline   Reply With Quote
Old 09-13-2010, 08:38 AM   #70
jjshabado
Carpal Tunnel
 
jjshabado's Avatar
 
Join Date: Jul 2006
Posts: 22,383
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by CopTHIS View Post
We actually never use contractors - either we do whatever is needed ourselves, or use specific software that has been developed for that purpose. (There is really one one job that we use that software for.)
This was really just my point. It seems strange to me that there's a demand for an Excel contractor since this is the attitude I would expect from most companies.

I mean obviously there's some demand since OP is making a living at it but I'm still surprised that companies have enough little Excel jobs to make it worth their while.

Edit: I guess I also feel like there's probably room to upsell a company like that to a better solution. If a company is constantly investing in Excel work week after week I have a hard time believing that Excel is the right solution.
jjshabado is offline   Reply With Quote
Old 09-13-2010, 09:45 AM   #71
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 Cueballmania View Post
Cool, thanks. I'll try it. Is there a way to solve the eigenvalue equation inside Excel?
I've never done is specifically, but there appears to be a lot of resources available:

http://controls.engin.umich.edu/wiki...icrosoft_Excel

http://lertap.curtin.edu.au/HTMLHelp...igenvalues.htm

http://people.revoledu.com/kardi/tut...igenValue.html

I only glossed over them because i've never calculated an eigenvalue before but it does seem possible

Quote:
Originally Posted by jjshabado View Post
Edit: I guess I also feel like there's probably room to upsell a company like that to a better solution. If a company is constantly investing in Excel work week after week I have a hard time believing that Excel is the right solution.
I did mention that i do spend a fair amount of time convincing companies to not use excel for certain processes
zomg is offline   Reply With Quote
Old 09-13-2010, 09:56 AM   #72
Ultim8Degen
banned
 
Ultim8Degen's Avatar
 
Join Date: Dec 2007
Location: Capucine FTW
Posts: 1,967
Re: Ask me anything about Microsoft Excel

I have been trying to take pdf files of past performances from DRF.com and move then into excel, but the files off of DRF.com are password protected. Is there anyway around the password?
Ultim8Degen is offline   Reply With Quote
Old 09-13-2010, 10:08 AM   #73
Ultim8Degen
banned
 
Ultim8Degen's Avatar
 
Join Date: Dec 2007
Location: Capucine FTW
Posts: 1,967
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
I agree, but i assumed other people would have some comments on it and I/others could learn, that's the point no? There's always going to be someone better at something but maybe they couldn't be bothered starting a thread.

I don't want to get defensive but most of the things I do are not specifically front end which is why I suck at pivot tables. It's probably because i've never done a job that required pivot tables, and find that using vba to manipulate data produces better data sets in my experience.
what is a pivot table?

what is it's functionality?
Ultim8Degen is offline   Reply With Quote
Old 09-13-2010, 10:11 AM   #74
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 Ultim8Degen View Post
I have been trying to take pdf files of past performances from DRF.com and move then into excel, but the files off of DRF.com are password protected. Is there anyway around the password?
Firstly, I know i've removed passwords from PDF fles before (i found some program on google) but never in excel and it would probably be a long process.

In saying that I just checked the site and while its hard to say without a log-in it seems like you can view the results in .PDF or just on the website, if you can get it to display on a website you can import it into excel using VBA. Click on the data tab (in 2007) and go to "import from web" then log-in and navigate to the table you want. If it works and you record it in the macro recorder you should be able to write some VBA to go around it to do it automatically.
zomg is offline   Reply With Quote
Old 09-13-2010, 10:16 AM   #75
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 Ultim8Degen View Post
what is a pivot table?

what is it's functionality?
It instantly creates a summary of large data sets so if you have a table with 10 columns and 50,000 rows you can create a pivot table on another sheet and it will automatically aggregate the data. It can be created via the Insert tab in 2007 then it will give you options of which column headings to show on the pivot table and you can drag and drop and move things around.

It's a very fast way to get totals/counts of rows and i think it is also good for quickly sorting
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 08:59 AM.


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