Major breakthrough for your training update!!! [Archive]

FrogMan

10-02-2003, 10:44 AM

Well, maybe not :) I posted about this in my training update post of last week, but most probably 98% of you didn't see it or didn't care about it, but hey I'm posting this for the 2% that cares :)

This is a simple spreadsheet that can help you see what has changed in the before and after pictures of your team, especially on the training update.

Fairly simple to use, it only needs three copy/paste for it to work. Two before the update and one more after the update is done.

Granted, this doesn't do anything that HAM or HattrickPoli can't do, but I designed this before I actually paid for HAM and I keep on using it at work, where I don't have HAM installed, but where Excel works pretty well :)

Anyway, you will find the file attached to this post. Take a look at the "INSTRUCTIONS" tab and you should find pretty much everything you'll need for this to work.

Please note that this sheet has been tested to return values in both "C$" and "US$". I'd appreciate if some of our European friends (3ric, Katon, Alf, anybody else over the big river) could test it and let me know if it works.

Hoping this is is of any use to anybody other than me, here it is.

FM

FrogMan

10-02-2003, 10:45 AM

For those of you wondering what it will looks like, here's a screendump of my training update last week, as it appeared in the comparison tool...

FM

TonyR

10-02-2003, 10:48 AM

Nice job FM. I'll deffinately have to give this a try

3ric

10-02-2003, 10:54 AM

Ok, I've done the first part, on the "before" tab I now have cell B8 with "Stamina:", cell C8 with "weak", cell D8 with "Keeper:" and cell E8 with "disastrous". The rows 3-7 are spanning the four columns B-E. Is that the way it should look?

FrogMan

10-02-2003, 11:01 AM

3ric, no this is not normal. All of you data should be in the B column.

1st question: what browser are you using?

Forgot to mention you need to use IE.

Second thing is you need to paste your data by right clicking on "B1" and selecting "paste as" or something like that (sorry I'm working with the French version of excel at work) which will give you three choices, you then select "text".

FM

FrogMan

10-02-2003, 11:03 AM

dola, this is how your "before" tab should look for it to work properly...

FM

3ric

10-02-2003, 12:41 PM

I see, I just copied and pasted with Ctrl-C, Ctrl-V. I'll try your way instead.

Edit: it looks much better, it's the second paste option, "Paste Special" you should use.

FrogMan

10-02-2003, 12:57 PM

3ric, thanks for the translation. That's what I meant in the instructions in the spreadsheet, when I wrote: "Copy it and paste it as text on the B1 cell of the "before" tab". Sorry I wasn't clear enough but I wanted to post it here before training had happened for most of you ;)

I just noticed a small mistake (or something kind of not perfectly clear) on the step 2 of the "before" instructions. They should read like this:
2) Still on your players page in Hattrick, highlight the short listing (the one with only the player names, the one on the right of the page). Copy it and paste it as text (using "paste special") on the A3 cell of the "COMPARISON" tab.

If you simply copy it with CTRL+C/CTRL+V, you may run into the same problem 3ric ran into with his first copy/paste.

FM

KevinNU7

10-02-2003, 01:29 PM

still not formulating out. I'm getting alot of #N/A and #Value! but I'm an excel guru myself so I'll fidn the bug.

FrogMan

10-02-2003, 01:31 PM

that's normal, that's only because your "after" is currently empty. Once the training update happens and you copy the "after" part, the numbers will magically happen :)

Just make sure your "before" looks like the one I posted above...

FM

KevinNU7

10-02-2003, 01:34 PM

I put in my "after". I just put in by before again (in tehf ater tab) and then chaged some values (i.e. made it looks like guys popped).

When I did that the spreadsheet showed that playmaking increased but didn't show the player value as increasing.

FrogMan

10-02-2003, 01:49 PM

This is weird. You say you actually changed the numerical value of the player in the "after" tab, right? I just retested it by doing exactly what you just said and it worked for me...

Here's the test I just did. I changed Scott Langston's value from "750 000 US$" to "754 000 US$" and wrote "inadequate" where his "wretched" for stamina was... The reults is attached.

One question to make sure the the parser sees the dollar values correctly. Here's how the line right below my first player's name look (with all spaces and everything):

750 000 C$, 19 years, inadequate form

My assumption is that for a player in with value in US$, it would look about the same except like this:

750 000 US$, 19 years, inadequate form

Can you tell me if this is right? I remember coming to this conclusion after seeing some copy/paste from ardent's team (thanks ardent for helping in the debugging) If this is right, there shouldn't be any reason why the spreadsheet wouldn't be able to show the value increase.

FM

KevinNU7

10-02-2003, 02:00 PM

That is right. One of my guys was,

138 000 US$

So I took my cursor and placed it between the 1 and the 3 and the hit 4. After that I hit delete and then enter so that way it said

148 000 US$

Not too worried about it. I saved the file with just the before data and I'm going to sue it for real tomorrow. FYI - I still like the way I do it better because I'm not a big fan of complicated formulas telling me what's going on (you never know if adding a row or something could fuck the whole thing up) and don't mind spending a few minutes each Friday entering my data.

FrogMan

10-02-2003, 02:10 PM

This is weird. I did basically the same thing you did with Scott Langston's value...

You and everybody else have all the rights to keep on using your way. As I said, this little spreadsheet is probably useless to anybody who has HAM or HattrickPoli, and there are probably as many ways of tracking changes as there are Hattrick players out there. I'm simply sharing what works for me. I'm not too worried about the adding of rows, as long as you add a full row. But this may be because I'm the one who wrote the somewhat long formulas in there ;) I usually make my excel spreadsheet in a way that inserting and/or deleting rows are not too much of a big deal... At least it hasn't been since I've started using this spreadsheet or my main sheet, adn I've been using them since I've started playing HT...

FM

KevinNU7

10-02-2003, 02:29 PM

cool. I'm a big Pivot Table guy and I find formulas really slow everything down so I kinda like to copy and paste alot into my data table and avoid formulas. Plus a get enough formula building at work that it is nice sometimes to just do the clicking and not have to write =IF(A1=B96,C96,D96)

FrogMan

10-02-2003, 02:44 PM

reason why I used the big formulas was because I wanted my sheet to parse through the very simple players page and give me back the players ratings without me having to enter anything other than the copy/paste of the said page. Doing the formulas could have been seen as a drag for anybody, but I had fun with them, maybe that's just me though, I'm like that :)

FM

daedalus

10-02-2003, 05:21 PM

You're one bad mother . . .

Mucho danke for sharing. :)

'xcept, ummm, I think my team broke the spreadsheet. The sheet only likes me to have up to 36 players. Heh. :D

sterlingice

10-02-2003, 05:52 PM

FrogMan- have you considered Hattrick Excel Users Anonymous? ;)

SI

FrogMan

10-02-2003, 06:13 PM

Originally posted by daedalus
You're one bad mother . . .

Mucho danke for sharing. :)

'xcept, ummm, I think my team broke the spreadsheet. The sheet only likes me to have up to 36 players. Heh. :D

OH MY GOD!!! Just went to look at how many players you have and :eek:!!!!

Don't you think 44 players is a bit overboard? :D

Anyway, it's quite simple to solve. Here's a version od the file especially made for supersized rosters :)

You can hid and unhide the lines in teh COMPARISION sheet to fit your roster size, but it can go up to 60 players :)

FM

FrogMan

10-02-2003, 06:15 PM

Originally posted by sterlingice
FrogMan- have you considered Hattrick Excel Users Anonymous? ;)

SI

I thought about it, but they wouldn't take me. They said they couldn't save me, so I crashed in here instead and I bug you all with my excel toys :)

FM

terpkristin

10-02-2003, 06:39 PM

FM-
Great file, I can't wait to try it out tonight! I had always wondered if there was a way to better track it in Excel, but when it seemed like I'd need to manually enter all the stuff (I'm not great with macros and parsing/comparison codes in Excel), I decided HAM would be better. I like yours in particular because it tells you the amount of gain/loss right off, where in HAM I'd have to find the player profile, etc.
This is why I support you dude, thanks again, it's awesome!
~tk

daedalus

10-02-2003, 07:18 PM

You the AMPHIBIAN! Mucho danke, Frogger. :)

I guess it wouldn't be the right time to say I've been selling off quite a few players of late? :D

Mr. Wednesday

10-02-2003, 08:20 PM

I must say, I consider myself somewhat skillful with Excel, but I wouldn't think of doing something like this without going nuts with VBA code (and, of course, slowing the whole thing down).

FrogMan

10-02-2003, 08:35 PM

Originally posted by daedalus
You the AMPHIBIAN! Mucho danke, Frogger. :)

I guess it wouldn't be the right time to say I've been selling off quite a few players of late? :D

Thanks for the kind words, daed. So you mean you had more than 44 at some point!!! :EEK: I thought I had many players when I had 28-29 on my roster :)

FM

FrogMan

10-02-2003, 08:38 PM

Originally posted by Mr. Wednesday
I must say, I consider myself somewhat skillful with Excel, but I wouldn't think of doing something like this without going nuts with VBA code (and, of course, slowing the whole thing down).

I'm not very big on VBA coding, maybe that's because I'm not a really programmer. I like playing with excel and the many formulas quite a bit though. I gues you are all beginning to know this, right :)

FM

FrogMan

10-02-2003, 08:40 PM

Originally posted by terpkristin
FM-
Great file, I can't wait to try it out tonight! I had always wondered if there was a way to better track it in Excel, but when it seemed like I'd need to manually enter all the stuff (I'm not great with macros and parsing/comparison codes in Excel), I decided HAM would be better. I like yours in particular because it tells you the amount of gain/loss right off, where in HAM I'd have to find the player profile, etc.
This is why I support you dude, thanks again, it's awesome!
~tk

Thanks tk. I hope you realized there was a part that needed to be done _before_ the update though :)

Don't want to hear from peolpe tomorrow morning telling me this is no good and totally useless because they didn't take a picture of their team before and after the training update...

FM

Mr. Wednesday

10-02-2003, 08:57 PM

Originally posted by FrogMan
I'm not very big on VBA coding, maybe that's because I'm not a really programmer. I like playing with excel and the many formulas quite a bit though. I gues you are all beginning to know this, right :)Oh, definitely. :D

That was a compliment, incidentally.

I've got a problem -- it didn't parse my player US$ values, at all. I loaded this in Excel 97, but I don't think that should have anything to do with it.

FrogMan

10-02-2003, 09:06 PM

Mr. Wednesday, I sent you a PM with an email address for you to send me your file so I can look at it.

Thanks for the compliment, that's how I had taken it ;)

FM

edit: typo

daedalus

10-02-2003, 09:28 PM

Originally posted by FrogMan
Thanks for the kind words, daed. So you mean you had more than 44 at some point!!! :EEK: I thought I had many players when I had 28-29 on my roster :)Heh. You'll be happy to know that once you're past 50 players on the roster, they put yellow card and warning on your "Players" page. Also, any player beyond the first 50 cannot be used in a game.

The things you learn! :D

terpkristin

10-02-2003, 10:17 PM

Originally posted by FrogMan
Thanks tk. I hope you realized there was a part that needed to be done _before_ the update though :)

Don't want to hear from peolpe tomorrow morning telling me this is no good and totally useless because they didn't take a picture of their team before and after the training update...

FM

FM, what do you take me for a blonde jock?

Oh, wait... :p :D

But I'm a rocket scientist so hopefully it makes up for some of the blonde....

~tk

FrogMan

10-03-2003, 09:56 AM

Originally posted by terpkristin
FM, what do you take me for a blonde jock?

Oh, wait... :p :D

But I'm a rocket scientist so hopefully it makes up for some of the blonde....

~tk

:D I hope the rocket scientist credits will make up for some of the blondness factor cause I'd hate to see a rocket designed by a fully blond jock :)

Anyway, email me your file, tk. I think I have found something that may cause some problems in older version of Excel (like 97 or something) but that Excel 2002 (what I have at work) and Excel XP (at home) can sort of correct by itself. I have sent a modified version of the file to Mr.Wed. for him to review and I'm fairly certain I found what was the problem.

FM

daedalus

10-03-2003, 02:06 PM

I think it rocks. Let's me see at a glance what changes happened. The only problems I still have left (well, with regards to the spreadsheet) is the values are still not coming up (#VALUE!) and for some reason, my last player is broken (#N/A for all value). But whatever. I heart what the spreadsheet does now. Hooray for not needing Poli to keep track of who popped anymore!

[And hooray for Miggie popping excellent! Rah! :D]

FrogMan

10-03-2003, 02:21 PM

daedalus, you got a PM regarding your problem...

FM

daedalus

10-03-2003, 02:47 PM

Originally posted by FrogMan
daedalus, you got a PM regarding your problem...Hey, cool! I always heard, ahem, "medications" were better priced over the border!

Edit: Oh, wait . . . you meant with HT-comp. That works, too! :D

Mr. Wednesday

10-03-2003, 08:05 PM

FrogMan has indeed fixed the value bug in Excel 97, so you should be straightened out in short order.

FrogMan

10-09-2003, 11:55 AM

With training coming up again today and the fact that I have fixed the value parsing problems for USA teams, this thread has earned a bump :)

Any USA team that had problems with the parsing of the player values, please download the new one I have attached to this message.

Also, if you get one broken player, like daedalus reporter, it may be that your player's name has one extra space at the end of it. This can sometimes happen to your very last player when you do the copy/paste from you player page. Simply edit the player name in your "COMPARISON" sheet by deleting the extra space and everything should work fine.

For any question, either PM me or post in here.

FM

FrogMan

10-09-2003, 11:58 AM

dola, I'd also be interested to hear from 3ric and other people from countries other than Canada and USA, to see if it worked for them or not. It should work, as long as you currency only has two characters, like "C$" and not three, like "US$". Don't know about that funky character for the euro though...

FM

edit: typos...

3ric

10-09-2003, 01:02 PM

I messed up the sheet last week and couldn't get back the "before" data, but I'll get it right this time!

Mr. Wednesday

10-09-2003, 09:01 PM

It turns out the fix for old Excel didn't work, but some poking around found the problem: The VALUE function doesn't like embedded spaces.

This modification (formula for the first row) does the trick:
=IF(A3<>"",VALUE(SUBSTITUTE(LEFT(INDEX(after!$B$1:$B$309,D3+1,1),FIND(",",INDEX(after!$B$1:$B$309,D3+1,1))-5)," ",""))-VALUE(SUBSTITUTE(LEFT(INDEX(before!$B$1:$B$309,B3+1,1),FIND(",",INDEX(before!$B$1:$B$309,B3+1,1))-5)," ","")),"")

3ric

10-10-2003, 01:59 AM

With my players, there was an error (#VALUE!) trying to calculate changes in the player values.

They are valued in "kr" as below:
<small>
Your 26 players

Anders Vinell
50 000 kr, 19 years, poor form
Has disastrous experience and weak leadership abilities [Powerful]
Stamina: weak Keeper: disastrous
Playmaking: wretched Passing: poor
Winger: inadequate Defending: poor
Scoring: poor Set Pieces: passable

Andreas Stensson
1 450 000 kr, 23 years, passable form
Has poor experience and poor leadership abilities [Unpredictable]
Stamina: formidable Keeper: wretched
Playmaking: solid Passing: inadequate
Winger: inadequate Defending: poor
Scoring: poor Set Pieces: weak</small>

Vince

10-30-2003, 09:56 PM

Bump :)

FrogMan, now that I've figured out the Money issue with the new excel file, this sheet works perfectly. Thanks dude!

FrogMan

10-30-2003, 10:12 PM

I didn't figure out the issues since I wasn't really able to reproduce them :) What did you do, what Mr Wednesday said?

FM

Vince

10-30-2003, 10:16 PM

Yeah, just sub his formula in for each of the cells in the Value column, and it works fine. I think the issue was with white space for your parser (but I'm no excel hound, so I could be way off :))

FrogMan

10-30-2003, 10:20 PM

maybe... It's probably something that excel in some higher versions is taking care of it because I don't have that problem on my excel 2002... oh well, as long as it works for you :)

Welcome to my group of excel trainees ;)

FM

Mr. Wednesday

10-31-2003, 12:40 AM

What my formula does -- the money value that gets fed into the VALUE function in the original spreadsheet uses spaces as a thousands separator (e.g. "10 000"). Excel 97 doesn't like this. So I used the SUBSTITUTE function to remove the spaces.