Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Advanced Filter or VBA??

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Tasmania
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.



  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Tasmania
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •