Advanced Filter or VBA??

FredMFoley

Board Regular
Joined
Mar 18, 2002
Messages
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Upvote 0
Thanks for that. Knowing that it can be done that way is a great help, and I've nearly worked it out!
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top