Australian (ASX) Stock Market Forum

Excel Guru?

Jump to new
Re: Excel Guru ?

I have an excel spreadsheet that monitors all my holdings and buying/selling - but I have to manually work with it. I am thinking of writing some VBA to make it a bit more 'user friendly', when its done i'll keep you posted.

TJ
 
Re: Excel Guru ?

I've been looking for a similar programme- Comsec does a bit of record keeping but not too much.

Do some of the charting programmes come with it (eg Amibroker, Metastock??).

I don't have specific charting software, hence the question. Might save everyone some trouble if the software does everthing from charting to money management. Tech's thread on position sizing had some spreadsheets- maybe that's a good place to look?
 
Re: Excel Guru ?

Profitseeker,

we should be able to attach it here shouldn't we (under attachments ? Joe ?)

And that way we can make it interactive with input from everyone.

I was thinking of a sheet that you can keep track of shares held (i realise the price changes continually but maybe inputting each Friday pm would be a starting point) and showing your stop loss point

But more importantly to keep track of shares sold, inclusive of entry/exit brokerage and profits/losses position sizes etc..

At the end of the year you can decide on what to sell on june 30th and worth buying on july 1st and have a great sheet for your accountant (may help prove you're a trader :D )

Any thoughts or help appreciated
 
Re: Excel Guru ?

I've done up something..it's very basic though, i'll try and attach later

I was hoping some of the more serious traders would have something of assistance for us all?

Anyway maybe we can evolve something here
 
Re: Excel Guru ?

Well here is the barebones skeleton of something more to come;

You will need Excel 2000 or later (I think) and you need to enable macros. It is basically a simple way to track holdings and trades (paper or real). Don't convert any of your data into it, there are a lot of bugs in it - just try it out and see what its like.

Feedback is welcome....

The basic idea is to have a panel of control buttons at the top where you can do everything, Below that, a summary of all holdings and trades. The sheets are there for detail. There isn't much formatting - so it looks pretty ****ty and some of the data may look wierd as it may not be in the correct cell format.

I basically started this out to track shares, and I like writing VBA in excel, there are not many features in it yet so I want feedback on what people think would be good - I'm thinking;

Obviously;

An overview of profit/loss total
Bank account balance
dividends and franking

Possibly;

Link share purchases to more than one account
Buying shares on margin
charting of total P/L over time
charting of P/L per month
other charts
calculates brokerage for you depending on parameters you provide
calculate Tax position for year

For a lot of this stuff people will need to enter a date for every transaction - will people do this????

cheers
TJ
 

Attachments

  • SharesVBA.zip
    38.2 KB · Views: 125
Re: Excel Guru ?

Hi Barbarian ... username/password a mystery ??? ... should be a simple download situation ... I checked it a couple of times.

I am more familiar with HC linking of files with HTML ... ASF does not allow?

I wanted to attach as below ... but .xls files not supported?

Suggest you go to the link in post above and paste into a separage browser and hit enter ... is should auto download ... just click Save to your HDD.

If that fails ... give me an email addy (not ASF) and I shall send you the xls file as an attachment.

Cheers ...
 
Re: Excel Guru ?

rembrandt said:
Hi Barbarian ... username/password a mystery ??? ... should be a simple download situation ... I checked it a couple of times.

I am more familiar with HC linking of files with HTML ... ASF does not allow?

I wanted to attach as below ... but .xls files not supported?

Suggest you go to the link in post above and paste into a separage browser and hit enter ... is should auto download ... just click Save to your HDD.

If that fails ... give me an email addy (not ASF) and I shall send you the xls file as an attachment.

Cheers ...
Click to expand...

Rembrandt, from memory, your profile won't allow us to email you. :confused:
 
Re: JOE ?

The Barbarian Investor said:
Joe are we able to have a function to attach EXCEL ???
Click to expand...
Done... in less than ten minutes.

How's that for service? :D

Maximum file size is set to 100K. Let me know if this is too small. I don't know too much about Excel.
 
Re: Excel Guru ?

TjamesX said:
Well here is the barebones skeleton of something more to come;

Feedback is welcome....

For a lot of this stuff people will need to enter a date for every transaction - will people do this????

cheers
TJ
Click to expand...

Entering figures: if they aren't day traders or weekly traders I doubt it'll be a problem if people are serious about it. It'll have to be tested over time to be made simple and workable. You can always only enter the figures you need and ignore the other functions.

The other place that has a good sample of money mgmt entries is the Amibroker interface: http://www.amibroker.com/tour/report.html
If you could include those tools it'll be tops.
There is also a link on post #61 (of Tech's thread, below) from memory to simple VanTharp money mgmt calculations (see online tutes)- could we include that as a start (ie trade number, loss/profit, positive expectancy etc)?

Position sizing tools like the ones referred to in tech's thread would be good, I'm still getting my head around it, might be a bit difficult for you to do at this stage but maybe later:
https://www.aussiestockforums.com/forums/showthread.php?t=717&page=5&pp=10&highlight=position+sizing

Hope I haven't gone overboard in my wishlist but this type of data mgmt is crucial to success.
 
Re: Excel Guru ?

RK,

The basic idea is for it to be used by people who purchase shares on a reasonably regular basis, but not for day traders who are constantly buying/seeling - I am assuming they have management software integrated into their charting software.

I think the ideas you put forward about Tech's trading analysis, and displaying reports on particular shares is great. These can be added in reasonably easily as functions over time.

The way forward will be to have a workable program where you can enter you buys and sells, and it will display profit/loss and basic stuff. From here the data entered can be reused when/if I add functions over time for portfolio analysis etc. I will need to add an exporter/importer so that when updates are made you can transfer data across to the updated version.

I think I could have an initial version for people to enter their trades into maybe in a week.

Joe,

You may need to increase limit for excel files up to 300k, otherwise I can probably zip it

TJ
 
Re: Excel Guru ?

I would be interested in looking at a completed model of this excel programme - it is a wonderful thing to see people working together. :star: :star: :star:
 
Re: Excel Guru ?

Hi Barbarian ... I checked the link again today and it works fine ... it's a simple ten second download ... I am using Excel 2002 which is compatible back to '97.

I put up another file ... (http://www.users.bigpond.com/equus2/tradingbook.xls)

Out of interest ... does the username/password request you are getting look like a Microsoft .NET form ... if it is, I might send a please explain to MS Newsgroup ... naughty MS.

JoeBlow ... my .xls file is 185kb and is fully featured so as suggested by others maybe 300kb limit might be the way to go.

Cheers ... good luck folks ... that is about as much as I can do.
 
Re: Excel Guru ?

rembrandt said:
JoeBlow ... my .xls file is 185kb and is fully featured so as suggested by others maybe 300kb limit might be the way to go.
Click to expand...

Maximum .xls filesize now modified to 300K.
 
Re: Excel Guru ?

Hi JoeBlow ... thanks for that ...

I shall attach the .xls file and hopefully everyone will be able to get it.

Notwithstanding that the link can be pasted into a separate browser to save your bandwidth.

Cheers ...
 

Attachments

  • trading_book.xls
    184.5 KB · Views: 179

Similar threads

P
Managing my share portfolio buys/sells - CGT - Excel spreadsheets?
Replies
3
Views
4K
pizaz
P
A
First year of "value" investing in review
Replies
15
Views
9K
Anglers Rest
A
M
My personal take & experience on trading courses
Replies
2
Views
5K
wayneL
wayneL
darkhorse70
The Beginner Trader's Manual Guide
Replies
9
Views
20K
commoyo
commoyo
Rider
My Learning Curve
Replies
2
Views
3K
CanOz
CanOz
Top