| Author |
Message |
     shawky
Member
Username: shawky
Post Number: 457
Registered: 11-2003Rating: N/A
Votes: 0
|
| | Wednesday, January 26, 2005 - 06:21 pm: |  
|
Hi all
I have set this thread up for the purpose of focusing on the trade management for both games so that the primary threads do not get cluttered with Excel speak.
I will post the spreadsheets including upgrades and helpful hints here and all are welcome to ask/answer questions to assist each other manage our HOF Portfolios efficiently.
There are three primary spreadsheets:
1. The Hall Of Fame Game 1 Portfolio Manager (out dated)
2. The Hall Of Fame Game 2 Portfolio Manager
3. The Hall Of Fame Bank Template
Don't get phased by the detail here or in the spreadsheets. They are quite simple and obvious once you have a go.
Attached is the initial Hall Of Fame Bank Template. The Bank contains all trading transactions and for both games supports: Buy, Sell, Withdrawal, Deposit and Dividend transactions.
The Portfolio Manager (stay tuned to this channel) imports/exports the Bank for coordination of multiple portfolios and streamlined upgrades to the Portfolio Manager itself.
Your Instructions
1. Download the Hall Of Fame Bank spreadsheet
2. Have a look at how I entered my trades for the previous game (I was PA=16)
3. Print the spreadsheet out for later reference if you wish
4. Delete all of my trades so that there is space for yours (only the white cells though, not the yellow cells that contain formulas)
5. Enter your initial trades (maximum of 10) and also a deposit for $100,000
6. Save the spreadsheet as "Hall Of Fame 2 Bank 16.xls" replacing the number 16 with your PA Number.
The column PA (mentioned above) refers to Portfolio-Account and is the magic number that I will assign to all players in due course. You must use this PA number with all trades, otherwise the Portfolio Manager will confuse trades between different players.
regards
Shawky
|
shawky
Member
Username: shawky
Post Number: 458
Registered: 11-2003Rating: N/A
Votes: 0
|
| | Wednesday, January 26, 2005 - 10:46 pm: |
|
Hi Game 2 players
I have spotted requests for membership by the following people. The PA numbers that you will need to identify your individual trades in the spreadsheet are defined below:
1. Aussie 200
2. Archer
3. CJ
4. Gohard
5. Greywolf
6. Hilarius
7. Ingot54
8. Ken
9. Magneto
10. Marsupial
11. Nightstalker
12. Peterloh
13. Rederob
14. Stevo
15. starboard_tack
16. Shawky
17. Stun
For example, say Magneto wishes to commence trading next week by opening a long position on RCD using Friday's closing price and go short on PBB on Monday on the open (assume the opening price will be 2.88), with GSLO for the short position only; then here is how the spreadsheet would be populated:
Note that once the Price has been entered, the Assitant column estimates the buy quantity at 1580. Enter 1500 into the buy quantity (i.e. round down to the nearest 2 significant figures)then add brokerage and GSLO costs as appropriate as shown below:
In the picture above, the sell order to go short on PBB is added. 5200 shares will be sold (round down 5270 as before) resulting in:
And that is all there is to it. You should all be able to enter your initial positions by the end of the week. Don't worry if you have trouble, just post a query and I'm sure we will have things sorted before the end of next week.
regards
Shawky
P.S. Astute traders may have spotted a little error in the Trade Value calculation for the short position. The GSLO fee was not included, but that bug has now been fixed. This column is not used in any calculations merely there to assist/hinder. There is no need to fix it in your spreadsheet, as it will be fixed by the Portfolio Manager. If you wish to edit the formulas for column R the corrected formula for Cell R4 is:
=IF($F4="Buy",$H4*$G4+$I4+$J4, IF($F4="Sell",$H4*$G4-$I4-$J4,0))
Copy this to all subsequent rows in column R.
|
shawky
Member
Username: shawky
Post Number: 462
Registered: 11-2003Rating: N/A
Votes: 0
|
| | Saturday, January 29, 2005 - 04:03 pm: |
|
Attached is my Hall Of Fame 2 Bank Details if you wish to examine it in more detail to assist you to fill in your spreadsheet. I added an additional calculation column to assist checking that you don't over spend your 100k.
Here is the excerpt from the spreadsheet:
Take particular note that I rounded the trades to the nearest two significant figures (mind you this is not mandatory, but I have found it easier to trade if the figures are rounded). Some trades cost just over 10k, some just under BUT I still have $1002 left in the bank.
Once you have completed your spreadsheet, ensure it is named according to the instructions then email it to hof2@ozemail.com.au
Example spreadsheet name: "Hall Of Fame 2 Bank PA.xls" where PA is replaced by your Portfolio Account ID.
regards
Shawky
|
gohard
Member
Username: gohard
Post Number: 226
Registered: 05-2003Rating: N/A
Votes: 0
|
| | Sunday, January 30, 2005 - 11:28 am: |
|
Morning Shawky,
I have said this before and will do so again "this is truly a great job you are doing" ,we fully appreciate the effort.
The spread sheet works fine, for those of us that are new to trading it gives a great incite into excels capability.
Would I throw a spanner in the works if I asked is it possible to add the ability to set a stop loss.
I'm thinking for it to be in a two columns format one containing the COD,[close of day price], and a second showing a percentage from 1to10 or what ever, in a arrow selection box, including a formula to calculate the result.
Thus allowing each player to choose accordingly,the result is related to his own calculation expressed in the chosen percentage.
Each player to monitor his own result obliviously, but when the sheets are posted for collation each week it is a record of the activity.
Should you think it is unnecessary, it is just a thought, to try and get people who are trading or learning to, in my opinion,to understand one of most important parts of surviving in this business.
Example Heading COD S/Loss{arrow down box chose 7%}
1.50 1.40
Thanks once again for the chance to participate in this new game.I will post my selection spreadsheet today.
Cheers G
|
ingot54
Member
Username: ingot54
Post Number: 359
Registered: 05-2004Rating: N/A
Votes: 0
|
| | Sunday, January 30, 2005 - 03:24 pm: |
|
Shawky
On Friday 4th February, after one weeks trading, adjustments will need to be notified via a spreadsheet, if I have this right.
Is there another spreadsheet to use which will reflect changes in position - profit and loss?
If I simply change the price of the share, it has the opposite effect on balances - an increase in price reflects as a cost, not a profit, on current spreadsheet.
I never was kept in after school for maths, but probably should have been! Spreadsheets contain hidden terrors for me at the moment - have been on the MS Office site, furiously practicing their spreadsheet examples for Excel, but will probably have nightmares tonight.
If you don't have it, maybe someone else does - never be afraid to ask
|
nightstalker
Member
Username: nightstalker
Post Number: 709
Registered: 04-2004Rating: N/A
Votes: 0
|
| | Sunday, January 30, 2005 - 05:54 pm: |
|
Ivan - I'm with you. Spreadsheets have long been an absolute mystery to me, since the Visicalc days! However, Shawky's bank spreadsheet seems pretty easy to use, even for me, and I'm looking forward to getting the one where we actually manage the share prices, gains, losses, trades, etc, to see how difficult it is.
One problem for some may be that this exercise assumes the possession of Microsoft Office / Excel. For those who don't have it, there is an excellent alternative and it is FREE! Fully compatible with Microsoft stuff, including file formats.
It is OpenOffice, and can be downloaded FREE (did I say that already?) from:
www.openoffice.org
It is a 64 Mb download, and is also available for Linux as a 76 Mb download.
Very handy to have.
Hope that is useful for those who don't particularly want to feed Bill Gates' Super Fund!
Regards, NightStalker
"The trend is your friend till the bend at the end"
|
shawky
Member
Username: shawky
Post Number: 467
Registered: 11-2003Rating: N/A
Votes: 0
|
| | Monday, January 31, 2005 - 12:58 pm: |
|
Ingot
I'm not sure if that could be achieved nor would it be suitable to all traders. Ken also asked about stops, so this is what I propose:
The Bank Spreadsheet and subsequently the Portfolio Manager have provision for 4 User Fields found in Columns L to O. Many traders have both a Target Price and a Stop Price, and some may have multiple stop prices ( a closing price stop and an oh sh.t get out now price - the GSLO might fall in the later category)
Let's make Column L the "Target Price" and Column M the "Stop Price". If you use GSLOs then the Stop Price applies to the GSLO. If you have a closing price stop and a GSLO then I suggest you utilise either Column N or O for recording the other stop.
Regarding calculating stops on the fly that sort of thing would be possible in the Portfolio Manager.
Thanks Nightstalker for alerting people about OpenOffice.
I'd say it would be OK for a CSV file to be used instead of an XLS for the Unix community here. The import could support it but additional work I'd prefer to avoid.
Ingot
Yes there is another spreadsheet (Portfolio Manager) that imports and exports everyone's bank spreadsheet. So it is not wise to go and make changes to the bank spreadsheet layout. Changes to that layout can only be achieved vie the Portfolio Manager import/export facility. Once I finish off the Portfolio Manager, it will be posted. For the moment import is manual so that is how I will keep track of all portfolios.
Any values (including formulas) you put in the User Fields will be imported and subsequently exported if I upgrade the Bank Spreadsheet setup. Requests for formulas to be added to the Trade Calculation Assistant columns will be considered and implemented where practical. For example the assistant could easily calculate the price 7% below the purchase price if that is useful. A bit harder to make it user configurable as I would need to store your personal trading parameters, and that ain't on the list at present.
Ingot (and everyone)
The Bank Spreadsheet you are using should ONLY include Buy and Sell entries. It is not intended for this spreadsheet to provide you with a portfolio valuation. The Portfolio Manager provides your profit and loss etc in great detail. Stay tuned to this channel...
Oh
Spreadsheets are being received now. Always send the spreadsheet with the same name as defined in my instructions above. If you like add a date or comment to one or more of the free cells on Row 1. I will email individuals with feedback during the week.
regards
Shawky
|
nightstalker
Member
Username: nightstalker
Post Number: 712
Registered: 04-2004Rating: N/A
Votes: 0
|
| | Monday, January 31, 2005 - 06:17 pm: |
|
"
Thanks Nightstalker for alerting people about OpenOffice.
I'd say it would be OK for a CSV file to be used instead of an XLS for the Unix community here. The import could support it but additional work I'd prefer to avoid."
Shawky - OpenOffice reads and writes XLS files directly, and will export and import Excel files from Unix, Linux, or whatever, if I'm not mistaken. So people can still use Excel spreadsheets but in the free, OpenOffice environment, no matter which operating system they use.
Hope that's useful info for those not owning Microsoft Office.
Regards, NightStalker
"The trend is your friend till the bend at the end"
|
smallworld
Member
Username: smallworld
Post Number: 134
Registered: 01-2004Rating: N/A
Votes: 0
|
| | Monday, January 31, 2005 - 08:25 pm: |
|
Shawky
What email address could I use to send the spreadsheets to you.
cheers
|
nightstalker
Member
Username: nightstalker
Post Number: 713
Registered: 04-2004Rating: N/A
Votes: 0
|
| | Monday, January 31, 2005 - 08:46 pm: |
|
Forgive me butting in, but it's in the third post from the top:
" Once you have completed your spreadsheet, ensure it is named according to the instructions then email it to hof2@ozemail.com.au
Example spreadsheet name: "Hall Of Fame 2 Bank PA.xls" where PA is replaced by your Portfolio Account ID. "
Hope that helps 
Regards, NightStalker
"The trend is your friend till the bend at the end"
|
smallworld
Member
Username: smallworld
Post Number: 135
Registered: 01-2004Rating: N/A
Votes: 0
|
| | Monday, January 31, 2005 - 10:46 pm: |
|
thank you.
SW
|
nightstalker
Member
Username: nightstalker
Post Number: 714
Registered: 04-2004Rating: N/A
Votes: 0
|
| | Monday, January 31, 2005 - 11:15 pm: |
|
Pleasure
Sorry if I sounded like I was being a smarta*s - I wasn't trying to be, but it may have come across that way.
Regards, NightStalker
"The trend is your friend till the bend at the end"
|
banksia
Member
Username: banksia
Post Number: 3
Registered: 12-2004Rating: N/A
Votes: 0
|
| | Monday, January 31, 2005 - 07:00 pm: |
| | |
