+ Reply to Thread
Results 1 to 2 of 2

Retrieves lines of a table thanks to the date

  1. #1
    Registered User
    Join Date
    07-21-2011
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    15

    Retrieves lines of a table thanks to the date

    Hi all

    I have a sheet where I enter all my trades (Trade date, name of the stock, quantity and price ...). In this tab, all trades are entered, for all the different stocks (Tab "DEALS"). In the example sheet attached, only two stocks are entered LBTYA US and LBTYK US.

    I have created a tab for the stock LBTYA US (Name of the strategy : Here "EFG_LBTYA_US") where I would like Excel to retrieve all the trades details correponding to that particular stock traded (here, LBTYA US Equity, cell B2 in yellow in the tab).
    The original idead I had was to ask Excel to retrieve the data by extracting the dates it finds in the tab "DEALS" with the following formula :

    First trade date (In Cell(A11))
    =SMALL(INDIRECT($L$1),1+COUNTIF(INDIRECT($L$1),"<="&$L$2)) (=> INDIRECT(L1) in the column with all the trades date un the tab "deals")

    Then I used this, to find out the next date for which I have a trade : (Cell(A12), Cell(A13))

    =IF(OR(A11="",ISERROR(SMALL(INDIRECT($L$1),1+COUNTIF(INDIRECT($L$1),"<="&A11)))),"",SMALL(INDIRECT($L$1),1+COUNTIF(INDIRECT($L$1),"<="&A11))) which basically starts by taking as refrence the first date (Cell A11), and from there, retruve the next available date.

    The problem I have, is that I have only one date : So If I trade the same stock a couple of times the same day, my formula will retrieve only the first deal entered that day and I will miss all the others. For example, In the current version of the sheet, I only have the +14250 buy order on the 27/01/2014 and I a missing the -63500 sale order on the same day.

    To fill the rest of the table, I plan to use some vlookup ou sumproduct stuff (each name + combination). But as start I need Excel to find all the trades I have done for a particular stock within my trade list and sorts it by date, including all the trades done for each day (does not work at this time!). Unless you guys have a more clever solution to retrieve all the needed infos.

    Thanks a lot
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Retrieves lines of a table thanks to the date

    Hi,

    Instead of trying to do this with formulae, why don't you use Data Advanced Filter?
    You'll need to make sure your column labels on the EFG...sheets are the same as the data sheet.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Retrieves a value less than 180
    By Dougiebn in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 08-28-2013, 09:11 AM
  2. Is it possible in excel to have a macro that retrieves lists?
    By Jigyflyuk07 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-19-2013, 11:02 AM
  3. Essbase retrieves
    By Cecile Sule in forum Excel General
    Replies: 0
    Last Post: 12-13-2011, 12:00 PM
  4. Search Box that searches and retrieves data from another sheet
    By tommib in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-05-2009, 01:26 PM
  5. Formula retrieves wrong data
    By Newmoon in forum Excel General
    Replies: 2
    Last Post: 08-11-2005, 11:02 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1