ASX stock scrubber spreadsheet, page-10

First 10 Last

Share
Profile

Follow
Whataboutthisone

65 Posts.

2

15/05/18

15:42

Post #: 33109345
Share

Originally posted by roaminoz ↑

In your current holdings tab, you should put in the stock code as your reference to make the changes to the sheet rather than the company name. Unless one knows the exact company name value the table will not populate. Since everyone knows their stock code and after all it is only 3 characters it is a much better choice for the users

I see you have used vlookup as your formula in the table. This works fine in your way of set up, but you should use the index/match formula. Doing so allows you to look up column B, but return results from column A. A task that is impossible for vlookup to do.

Formula For A4 in portfolio sheet
INDEX(ASX!$A$4:$A$2407,MATCH('Current Holdings'!B4,ASX!$B$4:$B$2407,0))
You can then copy that and paste it down the column from A5 to A25.

This then allows the user to change column B, the rest of the table (vlookup) can stay the same as once company name (column A) populates the columns C to M will populate.

---------------------------------------------------------

In sheet where you have value errors, use the If(Iserror() function. This is just easy on the eye if a cell is blank compared to #value in the cell.

Formula would be Cell:ASXH4 = is(iserror(D4)=true,"",D4/D4*100)

Not sure how D4 is populating, probably through a marco/or Data Connections is grabbing data from a website?

-------------------------------------------------------

I see you only have the ability to search ASX stocks in the current holdings sheet, if you wish to also have the ability to search NASDAQ or NYSE you'll need to add another column in the current holdings probably between A and B to have which stock exchange you wish to search upon. Then use the indirect formula to search a certain sheet on your choosing.

The user will have to put in their stock code and also the stock exchange
Col B = ASX ____ Col C = TLS = ASX:TLS
Col B = NYSE ___ Col C = ABC = NYSE:ABC
Use the indirect formula to search the stock exchange sheet, and use the vlookup to lookup the code

------------------------------------------------------

All the above is just food for though anyway. Happy formulating.

Expand

Thanks roaminoz, thought there had to be a better way!
1 Upvote

Reply

Top Stories

WC8

Pan Asia rebranding to 'clear confusion' after bagging $35M for Chile projects

MIN

MinRes's Ellison knows he screwed up – for that reason, hunt for new Chair to be sped up

ALY

Alcidion inks deal with Vic health provider Peninsula for use of Miya Precision tech

GRL

To make modern missiles, you need 3D Printing. And in that gold rush, Amaero is selling shovels

BTH

Bigtincan's Board backs Investcorp offer: Why a NASDAQ listing could be game-changing

First 10 Last

ASX stock scrubber spreadsheet, page-10

Pan Asia rebranding to 'clear confusion' after bagging $35M for Chile projects

MinRes's Ellison knows he screwed up – for that reason, hunt for new Chair to be sped up

Alcidion inks deal with Vic health provider Peninsula for use of Miya Precision tech

To make modern missiles, you need 3D Printing. And in that gold rush, Amaero is selling shovels

Bigtincan's Board backs Investcorp offer: Why a NASDAQ listing could be game-changing

Featured News

Featured News

ASX stock scrubber spreadsheet, page-10

Top Stories

Pan Asia rebranding to 'clear confusion' after bagging $35M for Chile projects

MinRes's Ellison knows he screwed up – for that reason, hunt for new Chair to be sped up

Alcidion inks deal with Vic health provider Peninsula for use of Miya Precision tech

To make modern missiles, you need 3D Printing. And in that gold rush, Amaero is selling shovels

Bigtincan's Board backs Investcorp offer: Why a NASDAQ listing could be game-changing

Featured News

Featured News