| 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.
Incredible Charts
now with US Data
- AMEX, NASDAQ and NYSE data
- OTC BB and Pink Sheet stocks
- more than 500 market and sector indices
|
|
|
|
|
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: |
|
can I join?
|
smallworld
Member
Username: smallworld
Post Number: 136
Registered: 01-2004Rating: N/A
Votes: 0
|
| | Tuesday, February 01, 2005 - 07:56 am: |
|
nightstalker
Not at all.
|
ingot54
Member
Username: ingot54
Post Number: 388
Registered: 05-2004Rating: N/A
Votes: 0
|
| | Friday, February 04, 2005 - 08:44 pm: |
|
Shawky
Do we email a new spreadsheet with today's closing prices?
Or are you able to keep track the same as you did with HOF Mark I?
Hope everyone had a great week.
The only way to reach and maintain your "Dream Speed" is to begin at snail's pace.
|
shawky
Member
Username: shawky
Post Number: 474
Registered: 11-2003Rating: N/A
Votes: 0
|
| | Saturday, February 05, 2005 - 10:32 am: |
|
No Ingot
I keep track of current portfolio valuations. All you need to do is enter your Buy and Sell orders in the Bank spreadsheet, emailing me the updated spreadsheet whenever you change it but once a week is sufficient if you do trades during the week.
I'll post a report Sunday night.
Have a good weekend, I'm taking the weekend off for a change.
regards
Shawky
|
nightstalker
Member
Username: nightstalker
Post Number: 717
Registered: 04-2004Rating: N/A
Votes: 0
|
| | Saturday, February 05, 2005 - 10:39 am: |
|
Weekend OFF??? Sheesh - what a slacker!!! ;)
Just kidding, of course - you deserve a bloody month off with all the work you put into this project, not to mention your own trading Have a great time mate, and tell Mrs Shawky not to ask you to mow lawns and all that crap. Have a nap on the hammock, a glass of whatever, a good book, and a complete break from trading.
Then you'd better get your arse into gear on Monday - we're all expecting you to do your duty!! ;)
Regards, NightStalker
"The trend is your friend till the bend at the end"
|
shawky
Member
Username: shawky
Post Number: 479
Registered: 11-2003Rating: N/A
Votes: 0
|
| | Monday, February 07, 2005 - 09:10 pm: |
|
Why thanks Nightstalker
I spent a nice afternoon at Rosehill Racecourse, my first bet on the gee gees since I was a kid placing an each way bet on the Melbourne Cup with my grandfather the SP Bookie after finding out of him who was likely to be the winner. The atmosphere was good, the company entertaining and the food/beer at the right price.
I picked first place race 1, then last place race 2, put Mrs Shawky's pick on for race 3 which came 6th, so called it quits after that, paper betting the remainder. $50 was enough for Mrs Shawky and I being the last of the big spenders.
My host is a professional gambler and only risks 2% of kitty at any race meet, has a combination system that works quite well, has about 40% success rate when providing many opportunities, though the odds for the winners compensates substantially.
It goes to show, if you don't have a system and money management skills, you could go out backwards very quick.
An important note about the rules/mechanism of playing the game:
The spreadsheets should be emailed to me using the specific filename convention otherwise my Portfolio Manager can't be automated
The file name should be Hall Of Fame PA.xls where PA is your allocated portfolio number.
If there is a problem generating this filename on your computer system then let us all know and we shall select a simpler naming convention.
I will be working through the remainder of emails sent over the last few days, and replying to everyone.
regards
Shawky
|
shawky
Member
Username: shawky
Post Number: 488
Registered: 11-2003Rating: N/A
Votes: 0
|
| | Tuesday, February 15, 2005 - 06:39 pm: |
|
Hi everyone
Bank spreadsheets are being received - thanks. Below is the latest rendition of the "Hall Of Fame 2 Bank.xls" with my trades in it.
Only new players need use this spreadsheet (by deleting my trades then entering their own trades as per the previous instructions).
Leggie - download this spreadsheet and your almost up and running.
Existing players, download of this spreadsheet is not required.
regards
Shawky
|
shawky
Member
Username: shawky
Post Number: 501
Registered: 11-2003Rating: 
Votes: 1
|
| | Thursday, March 03, 2005 - 01:40 am: |
|
Hi everyone
We're almost there with the Portfolio Manager. I have attached the beta release of the individual User Portfolio Manager. Existing HOF 1 and HOF 2 game players will receive their Portfolio Manager via email inclusive of their own bank transactions.
Whilst the file below has been extensively tested, it is built on Microsoft products so no warranty is included. Anything can and will go wrong if the parameters on the Help sheet are not setup correctly.
For those not in the game, you may find some of the Excel stuff nifty or handy for your own trading. This spreadsheet will handle up to 25 portfolios though the charting functions and portfolio holding analysis have been excluded.
An export Bank macro is provided to assist manage the HOF games, but no import bank function is provided in this release. Support for import of Metastock style EOD data is also provided, but use at your own peril. The same sheet (EODPrices) will accept manual updates. Just follow the instructions.
HOF 1 players, once I receive your email address I'll send you your own Portfolio Manager. Though if you have kept track of your own trades you are welcome to try to import them into this spreadsheet.
I have included all of my trades from HOF 1 for people to see how the spreadsheet works.
regards
Shawky
P.S. The file attached is a Beta release only
|
gohard
Member
Username: gohard
Post Number: 246
Registered: 05-2003Rating: N/A
Votes: 0
|
| | Thursday, March 03, 2005 - 03:43 pm: |
|
Hi Shawky,
Started to work through the bank sheet and marvel at the effort you have achieved,(great job)would you confirm the time EOD data update can take place.
I also saw the column for S\L and wonder if the EOD formula could be copied to the spare col adjacent to the S/L column, reason being it would be easy to confirm and adjust the S/L if there was an increase in the price thus keeping the information corrected.
Does the decimal point in the columns need to be adjusted to 2 d/point i notice single entries e.g. 2 finish up as such but if you enter 1.23 it reads as written.
I hope these suggestions are not over the top.
Rapped in your effort thanks once again
Cheers G  
|
shawky
Member
Username: shawky
Post Number: 502
Registered: 11-2003Rating: N/A
Votes: 0
|
| | Thursday, March 03, 2005 - 06:05 pm: |
|
Hi Gohard
I don't think I have absorbed everything you asked but here is my first attempt at answering your questions:
1. EOD Data Update
The portfolio is valued on prices placed in the Close column of the EODPrices spreadsheet. How these prices (with corresponding ticker codes obviously) get there and how often is up to you. e.g. you can type them in or copy and past from a watchlist (e.g. ComSec Pro Trader or ETrade Pro)
For example, In ComSec Pro Trader if I copy and paste the watchlist, I have the "Last" column organised as the third column so I would change the EODPriceSource to 1 and EODPriceColumn to 3 on the Help page.
Of course my ComSec watchlist has too many columns and the copy and paste would overwrite the Help columns on the EODPrices sheet. I shall fix this in the next release. You can fix this yourself by inserting sufficient columns (say 5 more) before column M (i.e. in the Yellow shaded columns).
As for the Automatic EOD data import, the macro and Commands sheet rely on a Text File existing in the directory defined by MetastockImportDirectory. The file may be downloaded from various internet sites either after 6pm (if you pay them money) or usually the next day). The file format must include a heading row (which is ignored) and a row for each stock where the stock code is the first field and the close price is somewhere consistent after that (i.e. change EODPriceColumn to suit your data format).
My file looks like:
<ticker>,<dtyyyymmdd>,<open>,<high>,<low>,<close>,<volume>,CompanyName
AAC,20050302,1.91,1.95,1.91,1.95,579990,
AAE,20050302,0.19,0.19,0.19,0.19,6802,
but could just as easily look like
hi there hall of famers, here's your EOD data...
AAC,1.95,blah blah
AAE,0.19,bye bye
There is one catch. If EODPriceSource is 0 then the code assumes that column 2 includes the date in yyyymmdd format and that a row for AMP exists. Otherwise, your portfolio will not be valued correctly for the date specified (i.e. it is possible to look at your portfolio status a week ago by importing old EOD data. The date field is used to EXCLUDE trades executed after the EOD Data date.) Setting EODPriceSource to 1 simply uses the current date as the reference.
More answers to follow after I have a feed.
regards
Shawky
|
shawky
Member
Username: shawky
Post Number: 503
Registered: 11-2003Rating: N/A
Votes: 0
|
| | Thursday, March 03, 2005 - 07:21 pm: |
|
GoHard
2. Closing Price next to Stop Loss column
no it can't be placed in the spare column as the spare columns are purely for your use, not the Portfolio manager's. You are welcome to place your own formula in Column N to achieve this:
e.g. Cell N4 = AQ4
then copy the formula to subsequent rows but you will have to manage this yourself.
As an alternative I could include the current price under the Trade Calculations Assistant Section so that it is unnecessary to scroll across to column AQ to view the current price. Say between column Q and R?
Or, how about column W (currently spare) shows say the difference in % between the current price and your stop price. A +ve percentage means price is above your stop (or below for a short position) and a -ve percentage means the current price has dropped below (or above for a short position) your stop price???
Basically, any formulas (within reason, to keep the spreadsheet as simple as possible) can be included BUT they should be placed in coloured columns such that the "Admin - Update Formulas" button and associated macros handle them correctly.
3. Decimal Point adjustments
True and correct. I deliberately left the formatting as default for stocks over 50c need 2 decimal places whilst stocks under 50c need 3 decimal places and I find it irritating to see 3 decimal places on my ASX200 stocks. You are welcome to change it on your own spreadsheet. Just click on the columns "G : O" then select the menu Format -Cells - Category = Number, Decimal Places = 3.
If several players support the setting of 3 decimal places for all prices, I'll change the Master File for the next version.
4. Over the top?
If clients / friends / colleagues requests were always this easy, life would be a whole lot better!
regards
Shawky
|
shawky
Member
Username: shawky
Post Number: 504
Registered: 11-2003Rating: N/A
Votes: 0
|
| | Thursday, March 03, 2005 - 11:25 pm: |
|
Hall Of Fame 2 Players
I have emailed you all the Portfolio Manager V1.2.4 (very minor mods from V1.2.3)
regards
Shawky
|
gohard
Member
Username: gohard
Post Number: 248
Registered: 05-2003Rating: N/A
Votes: 0
|
| | Friday, March 04, 2005 - 10:10 am: |
|
Shawky,
I thought I had received the holy grail of spread sheets in that i could press on the nominated buttons and the world would come to me I don't have the luxury of E*Trade pro i only have std V so it is no trouble for me to load in manually I was reluctant to change the columns without your input for fear of upsetting the master sheet.
the decimals need only to be 2 points on the 2nd game as no share is under the 50c mark,as you suggest it is only an aesthetic issue anyhow.
Thanks for taking the time to keep us informed
Cheers G
|
shawky
Member
Username: shawky
Post Number: 508
Registered: 11-2003Rating: N/A
Votes: 0
|
| | Friday, March 04, 2005 - 12:59 pm: |
|
No probs Gohard
Basically the Portfolio Manager posted to all players in HOF 2 last night should serve for the rest of the game. It only has a few minor tweeks to that of the version posted above.
W.r.t breaking the spreadsheet. I am only concerned with the exported Bank File. So provided you do not insert new columns in between the Trade details (first 6 columns) then the code wont give a toss. As for all the other sheets, go for your life and make whatever changes suit you. Just test the functionality you need (i.e. the magic buttons) after each change, and kep backups.
Also, members have posted web links numerous times to free end of day data especially data available in Metastock format. ETrade should have it available even if you don't have Etrade Pro. Copy and paste from the website would work too, you might just have to tweak the EODPrices Sheet. The secret here is simply to ensure that the Range named "EODPrices" has the ticker code in the first column.
regards
Shawky
(Message edited by shawky on March 04, 2005)
|
magneto
Member
Username: magneto
Post Number: 87
Registered: 08-2003Rating: N/A
Votes: 0
|
| | Sunday, March 06, 2005 - 10:55 am: |
|
Hi Shawky,
That is one powerful spreadsheet application you have created. Thanks.
I am in the process of inputing the trades into the last spreadsheet you send.
Based on my read of the instructions,
a) I went to cooltrader.net and downloaded 4thMar's data and chose Metastock format. The file by default has a name of ms20050304.txt. I put it into C:\EODData.
b) Open the spreadsheet, click on tab Commands. In that sheet, I pressed "Import EOD Data" button. It came up with a "Compile error. Variable not defined" popup.
I must have stuffed up somewhere. Appreciate your assistance!.
Sincerely,
Mag
|
shawky
Member
Username: shawky
Post Number: 511
Registered: 11-2003Rating: N/A
Votes: 0
|
| | Monday, March 07, 2005 - 09:13 pm: |
|
Hi Magneto
The file has to be renamed 20050304.txt and double check that the variable MetastockImportDir is set correctly on the Help page (min you neither would generate the error you mentioned)
You could edit the code yourself to add the "ms" to the beginning of the file format. I'll see if I can include an option for this in the next version. This change can be made via the VB Editor in Excel (Alt-F11), selecting the Commands Sheet and modifying sub routine ImportEODData_Click to change the filename. The modified line of code is:
sSourceFileName = Worksheets("Help").Range("MetastockImportDir").Value & "\" & "ms" & sDate & ".txt"
As for the compile error, what version are you running? I would expect the spreadsheet to work with Excel 2000/2003 but am not sure about Excel 97.
If you try it again, select the Debug Option if possible and copy/email or post the two lines preceding and the line affected to the HOF email address. I have found that Excel 97 does not like some additional parameters passed to functions even though those parameters are not used. I actually thought I had fixed these errors last year for the EOD import, when testing the EOD Import facilities with another forum member. I will have a closer look to ensure the feature has not been re introduced.
regards
Shawky
p.s. When it comes to Microsoft Software, the stuff up usually relates to the lack of consistency with the MS products.
|
magneto
Member
Username: magneto
Post Number: 88
Registered: 08-2003Rating: N/A
Votes: 0
|
| | Wednesday, March 09, 2005 - 09:46 pm: |
|
Thanks Shawky,
I am not having much luck with it. My Excel is the 2000 version.
I have double checked that the file has been renamed to 20050304.txt and the variable MetastockImportDir is set to C:\EODData.
When I click on "Import EOD Data", it gives me a popup window saying, "Compile Error, Variable not defined". This popup is opened within what looks like a larger source debug window . In that source debug window, the string xlSortNormal is highlighted.
I suspect it might be the computer, so when I get the chance, probably during the w/e, I will try it on another computer and see how it goes.
Thanks for looking into this. Meanwhile, I will keep you posted.
p/s Have been tied up with the day job till late the last two days.
Sincerely,
Mag
|
shawky
Member
Username: shawky
Post Number: 512
Registered: 11-2003Rating: N/A
Votes: 0
|
| | Thursday, March 10, 2005 - 10:19 am: |
|
Magneto
This is a classic example of Excel compatibility issues. There is no way to force backward compatibility in the Visual Basic Editor for Excel (that I know of). The offending parameters appear to be DataOption1 and DataOption2 where the new value of xlSortNormal may not exist in Excel 2000. To fix the problem, when the compile error comes up click on Debug then make the following change wherever the compiler complains:
Existing code:
..."Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal"
Comment out the offending piece of optional code by inserting a single quotation mark before the comma:
..."Orientation:=xlTopToBottom ', DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal"
If you click elsewhere on the code to move the cursor away from that line the offending text will turn green as shown above. Now try the EOD import again.
regards
Shawky
|
magneto
Member
Username: magneto
Post Number: 89
Registered: 08-2003Rating: N/A
Votes: 0
|
| | Thursday, March 10, 2005 - 09:55 pm: |
|
Hi Shawky,
Good news!. Your analysis is spot on. Following your post above, viola! it successfully imported!. I will try to export out the data next, probably over the w/e.
Thanks a bunch.
Sincerely,
Mag
|
gohard
Member
Username: gohard
Post Number: 252
Registered: 05-2003Rating: N/A
Votes: 0
|
| | Tuesday, March 15, 2005 - 12:17 pm: |
|
Morning Shawky,
Help please!!!
Thought I would try to advance my knowledge and do the EOD thing signed up with CoolTrader to import the data saved to the folder as above, went to the commands sheet on my statement and typed in the date for yesterday moved to import button but the cursor turns to the four directional arrows.
Can't proceed past this point, is my sheet correctly formatted or have I changed something to stop this next move.
In visual basics that appeared if I double clicked on the button, in the general box I found this following line was highlighted in green is this the problem(' Set Convert Date to the next Date to use), this statement is in green in all the items in the drop down box on the left hand side.
Hope you can help me out when your time is available
Cheers G
|
shawky
Member
Username: shawky
Post Number: 514
Registered: 11-2003Rating: N/A
Votes: 0
|
| | Tuesday, March 15, 2005 - 03:14 pm: |
|
Gohard
It sounds like you are operating in Design Mode. To get out of this click on the "Design Mode" button on the Control Toolbox section of the toolbar. The button looks like a triangle and ruler.
Once you have done this, clicking on the Import button should execute the macros. Note that if you are using a version of Excel earlier than 2003 you may have to make the changes mentioned in my previous post.
regards
Shawky
|
gohard
Member
Username: gohard
Post Number: 253
Registered: 05-2003Rating: N/A
Votes: 0
|
| | Wednesday, March 16, 2005 - 11:28 am: |
|
Morning Shawky,
Followed through the instructions and clicked on the indicated button in control box yellow macro dialogue box appeared clicked ok but no change is there any point in my sending you my file for checking ?
Recently updated to Office 2003 haven't changed much as i am still working through the differences to 2000.
Thanks G
|
greywolf
Member
Username: greywolf
Post Number: 284
Registered: 04-2004Rating: N/A
Votes: 0
|
| | Wednesday, March 16, 2005 - 04:34 pm: |
|
Shawky,
Maybe you have come across the following already! if so apologies.
The formula in cell V345 double counts the Cash Balance, either Sum the column or take the difference of the two previous columns. NOT BOTH.
The formula in Cell AM345 should ADD the Cash Balance in Cell V345 to get a true total Balance.
regards
cheers
Keep this in mind; The Stock Market does not care what you or I think!
|
shawky
Member
Username: shawky
Post Number: 516
Registered: 11-2003Rating: N/A
Votes: 0
|
| | Wednesday, March 16, 2005 - 05:03 pm: |
|
Gohard, you can email it through. Are macros enabled in your excel security settings.
If you click on the grey button and it gets selected rather than executed, this means you are in forms or control edit mode. Somehow you must get out of design mode. Until this problem is solved there is no other way to execute the macro as it is a private macro within the sheet.
Greywolf
I sure there is some twisted reason for my formula, but I will look into it and get back to you.
regards
Shawky
|
gohard
Member
Username: gohard
Post Number: 254
Registered: 05-2003Rating: N/A
Votes: 0
|
| | Wednesday, March 16, 2005 - 05:46 pm: |
|
Afternoon Shawky,
Tried to fathom out the system and discovered the macro security was set to high so adjusted that reopened the file and when the debug came up it highlited the following in yellow found the reference to your other post but it was not highlited am i on the correct road do we have to make an adjustment to the yellowed area the imported file is in txt not xlm the status bar is suggesting it is importing the EOD data but that may be not the case.I will send the file in it's new state just in case
Cheers G
|
gohard
Member
Username: gohard
Post Number: 256
Registered: 05-2003Rating: N/A
Votes: 0
|
| | Thursday, March 17, 2005 - 11:11 am: |
|
Hi Forum Players,
I'm trying to take some work away from the Honourable Treasurer.
Working within the spreadsheet can anybody help me to configure the formula to provide the EOD adjustment for a stop loss of 7%.
This figure can be change by the individuals if required.
Shawky has allowed a couple of spare columns adjacent to the stop loss area,which we can use with his permission.
I copied the EOD formula and want to apply a s/l of 7%, * that formula by 93% and it's fine as long as the EOD price has increased.
What I want it to do, is recognise the price so that if it is lower then the previous day the S/L remains the same till the stop loss has been hit or breached !!!!.
Example Bought X share at 1.00, original S/L .93
Following day price increases to 1.03 S/L .95
Next day price drops to .99 S/L .95 remains that way till price increases to above 1.03 or hits S/L.
Is this possible I have limited understanding but have great faith in Excels ability.
Your input would be appreciated
Cheers G
|
gohard
Member
Username: gohard
Post Number: 257
Registered: 05-2003Rating: N/A
Votes: 0
|
| | Thursday, March 17, 2005 - 12:09 pm: |
|
Looking further than my nose, I see in the sheet Shawky has the following formula this shows the percentage stop loss at the time of purchase,it is in % not dollar value,it does not adjust for lower or higher just the original purchase price.
Is this part the way to suit the parameters i suggest.
Following is a copy of this formula.
=IF($G4>0,IF($F4="Buy",($M4-$G4)/$G4,IF($F4="Sell",($G4-$M4)/$G4,"")),"")
Hope i am not stepping outside my boundaries
Cheers G
|
shawky
Member
Username: shawky
Post Number: 517
Registered: 11-2003Rating: N/A
Votes: 0
|
| | Thursday, March 17, 2005 - 02:25 pm: |
|
Greywolf
I can't even find the offending formulas you are referring to. In an earlier attempt to export a bank spreadsheet for users I did place a summation formula at the bottom of the bank table to calculate the portfolio value. This didn't work properly (which might be what you are looking at) so I dumped that export method and decided to issue players with the full spreadsheet. I have emailed you the current version. Let me know if it still has the fault, referring to Sheet Name plus cell reference.
I will email players the latest version (1.2.5) after the end of this month when I process your trading spreadsheets for March. There are very few changes (since 1.2.3) and most will not affect players.
Gohard
The implementation of a trailing stop using historical data in the spreadsheet is a little tricky. I think it would require a macro added to the EOD Import. It could be implemented using a formula BUT would require you to manually copy the previous stop loss value to a spare cell. Then the formula can say something like:
If (newstop) > oldstop then use newstop else use oldstop. newstop would also need to be calculated in a separate cell. I might add this to the wish list but dont hold your breath.
regards
Shawky
|
hans
Member
Username: hans
Post Number: 4
Registered: 01-2003Rating: N/A
Votes: 0
|
| | Tuesday, July 05, 2005 - 11:35 am: |
|
Sorry I haven't offered anything to the forum over the year.I'm one of those that sits quietly on the side reading the discussions that are had and noting anything that might be of value to me to investigate further, it's been invaluable at times and hilarious at other times.You guys certainly enjoy what you are doing and I hope you continue to do so.I have taken the long term approach in this game, and it's been very hard to stick to my guns especially when some of my stocks take a plunge.I am confident that the stocks that I picked at the start are good companies, well managed and will overcome any short term downfall.My short term game is to stressfull for me and I find that the medium term gives me better results, but I can assure you it's hard sitting back and not pulling the trigger. Good luck everyone I'll be watching in the background.Hans.
|
