Advanced Filter or VBA??

FredMFoley · Mar 19th, 2002, 03:37 AM

I want to make a spreadsheet with a big list of shares (tickers or codes) in Column A, and the closing prices, say for every Friday, in the other columns, using past historical data.

I can download the historical data (which includes the closing prices) of all shares (1600 or so Australian shares) for the past date that I want, and use 'Import External Data' to get it into a new spreadsheet, in a form like:

Code Date Open High Low Close Volume
AAC 03/15/02 93 94 92 93 1269
AAR 03/15/02 3 3 3 3 2255
AAT 03/15/02 26 26.5 26 26 2437
AAU 03/15/02 90 90 85 85 26

Can I get Excel to automatically select only those rows that I want to keep from a list (eg AGY BHP BIL CML CRA DVN etc), (or discard the ones I don't want)?

I can then copy and paste the closing prices for that day into the main spreadsheet.

(Do I have to learn to use VBA??)

I hope my query is comprehensible!!
Thank You

Dave Hawley · Mar 19th, 2002, 04:16 AM

Hi Fred

While you could write a VBA procedure to do this is is not really needed. All you need to do is use Excels Auto Filter, filter down to the needed criteria and then select the entire table and copy. Excel will only copy the Visible cells.

You could also use Excels Advanced Filter and have the list transfered to another spot on the same sheet.

The help in Excel is quite good on Advanved Filters. Basically you just copy your headings to an unused area a put your criteria underneath the headings.

FredMFoley · Mar 19th, 2002, 10:04 PM

Thanks for that. Knowing that it can be done that way is a great help, and I've nearly worked it out!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 19th, 2002, 03:37 AM	#1
FredMFoley Board Regular Join Date: Mar 2002 Location: Tasmania Posts: 58	I want to make a spreadsheet with a big list of shares (tickers or codes) in Column A, and the closing prices, say for every Friday, in the other columns, using past historical data. I can download the historical data (which includes the closing prices) of all shares (1600 or so Australian shares) for the past date that I want, and use 'Import External Data' to get it into a new spreadsheet, in a form like: Code Date Open High Low Close Volume AAC 03/15/02 93 94 92 93 1269 AAR 03/15/02 3 3 3 3 2255 AAT 03/15/02 26 26.5 26 26 2437 AAU 03/15/02 90 90 85 85 26 Can I get Excel to automatically select only those rows that I want to keep from a list (eg AGY BHP BIL CML CRA DVN etc), (or discard the ones I don't want)? I can then copy and paste the closing prices for that day into the main spreadsheet. (Do I have to learn to use VBA??) I hope my query is comprehensible!! Thank You

Mar 19th, 2002, 04:16 AM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Fred While you could write a VBA procedure to do this is is not really needed. All you need to do is use Excels Auto Filter, filter down to the needed criteria and then select the entire table and copy. Excel will only copy the Visible cells. You could also use Excels Advanced Filter and have the list transfered to another spot on the same sheet. The help in Excel is quite good on Advanved Filters. Basically you just copy your headings to an unused area a put your criteria underneath the headings.

Mar 19th, 2002, 10:04 PM	#3
FredMFoley Board Regular Join Date: Mar 2002 Location: Tasmania Posts: 58	Thanks for that. Knowing that it can be done that way is a great help, and I've nearly worked it out!