Wednesday, April 9, 2014

yogi_Create A Summary Sheet To List All Unique EPIC That Still Has Opening Positions


                                         Google Spreadsheet   Post  #1593
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-09-2014
post by VincentCw: (https://productforums.google.com/forum/#!mydiscussions/docs/-nZUG6OxC_I)
How do I filter by column and selected rows?
TypeEPICCostQuantity
BuyLON:RMG120.0022
BuyLON:RMG123.0030
BuyLON:COST294.0633
BuyLON:AVV2,260.008
BuyLON:NRR283.4870
BuyLON:COST225.0011
BuyLON:NRR276.635
SellLON:AVV1,960.408

Hi guys, initially I only wanted to get unique EPIC which works fine (without involving the sell) with the following formula:
=unique(FILTER(B2:B,NOT(ISBLANK(B2:B))))

However, I now that I have included sell I wanted to select unique EPIC with no current open trades, meaning to get calculate the sum of all Buy and minus sum of all Sell for the unique epic and only return if the total sum is more than 0.

I've tried using the filter function but the filter isn't able to 'loop through' each unique epic to cross check against it's difference in the sum of buys and sells.

Any help is much appreciated.
---
Hi Yogi,
  Thanks for your quick response.


I've taken out the involving columns and rows and place it into a sheet.

EPIC is just a stock reference number that's used by stock exchange or even googlefinance() function.

a) i'm trying to list all unique epic that still has opening positions, this means that the buy - sell must be more than 0.

b) Cell A16 (current formula is =UNIQUE(FILTER($B$3:$B$10, NOT(ISBLANK($B$3:$B$10)))) )


c) expected result is in cell F15:H19
This is correct because LON:AVV has buy of 8 and sell of 8 hence the quantity is 0 therefore there's no outstanding trades and should not be listed in the summary sheet.

This is what I want to achieve:
=UNIQUE(FILTER($B$3:$B$10, NOT(ISBLANK($B$3:$B$10)),SUM(FILTER($D$3:$D$10,$B$3:$B$10=A16,$A$3:$A$10="Buy")) - IFERROR(SUM(FILTER($D$3:$D$10,$B$3:$B$10=A16, ($A$3:$A$10="Sell")))) > 0 ))

Getting the difference of buy and sell for each unique epic and to ensure it's more than 0.
However, you will get a circular dependency error, which is understandable.

Is there a way where you could get only unique epic, then loop through each of those epic to make sure they have > 0 quantity (opening position)?
-------------------------------------------------------------------------------------------------------------------------------------------------------


No comments:

Post a Comment