Excel Shares VBA

[TjamesX]

This is related to the Excel guru thread; https://www.aussiestockforums.com/forums/showthread.php?t=1057

I have attached the latest version of the excel program I have been working on - been using it for the past week and have imported all my past transactions over last 8 months and it seems to work without any major glitches.

What it can be used for;

monitoring Buys/sell shares
monitoring Dividends (+ franking credits etc)
Total Portfolio Return
Share trade return
Montoring linked shares account (funds transfers, interest, share buy/sell etc)
Web Server to automatically update share prices in portfolio and updates portfolio value
records all data in easy to get at page (Data page)

What it doesn't do yet;

Purchase shares on margin
charting
trading stats
......?

I know it works with Excel 2000 (I am unsure about other versions?)

I will be making updates from time to time and posting them in this thread - with any updated version the data can be transferred across using the import function.

If you have any queries/questions/requests post them in this thread and I will answer them.

Other than that.... Have fun

TJ

 

[Porper]

This is related to the Excel guru thread;

I have attached the latest version of the excel program I have been working on - been using it for the past week and have imported all my past transactions over last 8 months and it seems to work without any major glitches.

What it can be used for;

monitoring Buys/sell shares
monitoring Dividends (+ franking credits etc)
Total Portfolio Return
Share trade return
Montoring linked shares account (funds transfers, interest, share buy/sell etc)
Web Server to automatically update share prices in portfolio and updates portfolio value
records all data in easy to get at page (Data page)

What it doesn't do yet;

Purchase shares on margin
charting
trading stats
......?

I know it works with Excel 2000 (I am unsure about other versions?)

I will be making updates from time to time and posting them in this thread - with any updated version the data can be transferred across using the import function.

If you have any queries/questions/requests post them in this thread and I will answer them.

Other than that.... Have fun

TJ

Tjames,

This has been exactly what I have been looking for, thanks for making it available..As a newbie and being totally Excell illiterate it is a god send.

I started trading last year and now have to do my tax return, a bit different because I live in New Zealand.I went to see my accountant today and was told to "tidy up" my spreadsheets, plus I was doing the calculations incorrectly.Anyway, I have had a play and it will let me add a stock, it automatically updates the price etc, but if I try and sell that stock all I get is an error "run time error 6", now this could be me as I am a total beginner but I think I am doing it correctly, any ideas ? Just had another go, no joy.Thanks.

 

[TjamesX]

Tjames,
I have had a play and it will let me add a stock, it automatically updates the price etc, but if I try and sell that stock all I get is an error "run time error 6", now this could be me as I am a total beginner but I think I am doing it correctly, any ideas ? Just had another go, no joy.Thanks.

I have been using it for a while and have had no problems with it so far. Would you be able to send me the spreadsheet that you have to;

tj at inshape.com.au

My first assumption would be that you are using a different version of excel (I have made it using excel 2000). Which means there may be functions/operations in the Visual Basic Code that do not work under different versions??? but there is no way to test this easily..... I have access only to 2000 version.

Ill see if I can recreate the problem with the one you send me.

TJ

 

[TjamesX]

After a bit of investigation I found Porper's problem. It had to do with the number of shares sold in one parcel - if you have cent or lower shares you could be selling a large number, which maxed out the int data type, so I have changed the data type to long, you are now limited to sell I think 2,000,000,000 shares at any one time

The fixed version is posted below.

TJ

 

[pete152]

Hello,
To run this do I have to run macros?
Thanks ,
Peter

 

[TjamesX]

Yep

 

[pete152]

Thanks,
Peter

 

[xyz246]

Hi TjamesX or RichKid
This is a pretty old thread, but I am interested trying to do a little programming and looking at other things aside of Amibroker. I notice your SharesVBA is passworded...is there a chance that you could open up the VBA so I can have a look at it. It is pretty old but I only run Micro Excel 2000.
Thankyou.

 

[IB12]

What's the password?

 

[redcorvetteguy]

nice spreadsheet, will be good to take a closer look. i have made one that does similar so will be interesting to compare.

 

[bill90]

nice spreadsheet, will be good to take a closer look. i have made one that does similar so will be interesting to compare.

I am currently trying to write a vba macro that will be able to calculate sold shares that werepurchased at different times (FIFO). I have a work area that calculates based on price per share. I can do it easily by hand, but writing the code is driving me batty. Do you think I could take a look at your macros or workbook for some ideas. I have my flowchart in my head which basically would copy in buys until shares are =< than buys, but I have a difficult time getting it into code and then setting up price per share to show each buy p/l. (my email is bill.sheff@gmail.com and would appreciate any help). Thanks