Dear friends,
My 2 cents..
Here is an improved version from Amit's version of Excel Stock Tracker.
Amit - apoligies if I released it before you got time to release it but I did the modifications and like you said I wanted others also to benefit from it.
Changes from Amit's version.
1. All stocks are now transposed into rows than columns. (I prefer Amit's version though. The visual of a stock's high/low is better)
2. Fixed some bugs in Amit's version, Last temp column in the sheet is not deleted thus making the file size bigger.
3. In the Current holdings sheet, additional columns are added viz., buy quantity, buy price, a profit loss calculation, profit/loss %, and 52 Week high/low columns are added. 52 Week high and low are automatically retrieved from Yahoo. For some of the scrips, yahoo is returning 0 (Zero) as the 52 week high and low. In these cases the values are not updated and you can use other means to update those values. When you hit Get Data again, it will not overwrite your previous values with 0 (Zero).
4. In the above you cannot change the order of columns where automatic calculation is done if you want the sheet to work properly.
5. You can change the columns buy quantity, buy price, profit loss calculation to whatever you want but the number of the columns before 52 Week high and 52 Low should be the same meaning 52 Week high should always be the 5th column, 52 low should be the 6th column and get data should be the 7th column. Look at the changes in the Watchlist you will get it. It is basically a copy of Current holdings with columns definitions changed as per what I want to track.
6. Intraday sheet is also now transposed row/column wise.
7. A new sheet with volumes changes and support resistance indications and alerts is also included. Along with a stock's quote the volume on a daily basis is also downloaded and uploaded. The change in volume is also as per previous formatting for quotes. Please note that you have to key in the support and resistance of each stock manually. Depending on what value you key in these, the formula will fetch the today's traded value, and suggets if it is a "sell" candidate or "Hold" candidate. You can change what you want to be shown here if you edit the formula in the formula bar. Please note you have to copy this formula after editing to all rows if you want the changes to be reflected in all rows. If you want to add new scrips, please copy a previous row from columns A to G and paste in the new row and change the scrip name and scrip code and hit get data. It will update the automatic values. This will also automatically copy the formulas for Support/Resistance without any changes required from your end.
8. A sheet with all Yahoo symbols is included.
9. Password protection for the source code macro is removed.
I have tried to implement as many changes as were asked in Amit's thread but some of them could not be implemented. If you need some modifications do let me know and I will try, but please note work comes before this.
As always feedback is welcome and let me know if there are any bugs.
Regards
Satish
Updates:
1.5 : Original Version
1.6 : Minor bug fixes and BSE Yahoo symbols added.
1.7 : Minor bug fixes and profit/loss% added.
My 2 cents..
Here is an improved version from Amit's version of Excel Stock Tracker.
Amit - apoligies if I released it before you got time to release it but I did the modifications and like you said I wanted others also to benefit from it.
Changes from Amit's version.
1. All stocks are now transposed into rows than columns. (I prefer Amit's version though. The visual of a stock's high/low is better)
2. Fixed some bugs in Amit's version, Last temp column in the sheet is not deleted thus making the file size bigger.
3. In the Current holdings sheet, additional columns are added viz., buy quantity, buy price, a profit loss calculation, profit/loss %, and 52 Week high/low columns are added. 52 Week high and low are automatically retrieved from Yahoo. For some of the scrips, yahoo is returning 0 (Zero) as the 52 week high and low. In these cases the values are not updated and you can use other means to update those values. When you hit Get Data again, it will not overwrite your previous values with 0 (Zero).
4. In the above you cannot change the order of columns where automatic calculation is done if you want the sheet to work properly.
5. You can change the columns buy quantity, buy price, profit loss calculation to whatever you want but the number of the columns before 52 Week high and 52 Low should be the same meaning 52 Week high should always be the 5th column, 52 low should be the 6th column and get data should be the 7th column. Look at the changes in the Watchlist you will get it. It is basically a copy of Current holdings with columns definitions changed as per what I want to track.
6. Intraday sheet is also now transposed row/column wise.
7. A new sheet with volumes changes and support resistance indications and alerts is also included. Along with a stock's quote the volume on a daily basis is also downloaded and uploaded. The change in volume is also as per previous formatting for quotes. Please note that you have to key in the support and resistance of each stock manually. Depending on what value you key in these, the formula will fetch the today's traded value, and suggets if it is a "sell" candidate or "Hold" candidate. You can change what you want to be shown here if you edit the formula in the formula bar. Please note you have to copy this formula after editing to all rows if you want the changes to be reflected in all rows. If you want to add new scrips, please copy a previous row from columns A to G and paste in the new row and change the scrip name and scrip code and hit get data. It will update the automatic values. This will also automatically copy the formulas for Support/Resistance without any changes required from your end.
8. A sheet with all Yahoo symbols is included.
9. Password protection for the source code macro is removed.
I have tried to implement as many changes as were asked in Amit's thread but some of them could not be implemented. If you need some modifications do let me know and I will try, but please note work comes before this.
As always feedback is welcome and let me know if there are any bugs.
Regards
Satish
Updates:
1.5 : Original Version
1.6 : Minor bug fixes and BSE Yahoo symbols added.
1.7 : Minor bug fixes and profit/loss% added.
Last edited:
