Please help me with my excel project. I have a stock trading spreadsheet with a database I export from my trading platform sorted vertically by execution date (regardless of whether Its a buy or a sell). I am trying to think of a way to dynamically transpose my database horizontally, grouped by their corresponding ticker symbol and sorted out from left to right by date (see link for desired transposed data image below. I also attached a sample data spreadsheet).
Raw data pulled from platform:
link: i.imgur.com/6Gt94gm.jpg
*ignore the following header: Order Type, Pos effect and Spread.
Desired dynamically transposed data:
link: i.imgur.com/Bs0G5VF.jpg
*"Filled" header contains the entry prices and "Cover" contains the exit prices. "Shares" are the quantity.
Spreadsheet structure:
- Each trading cycle has their own single row sorted by execution sequence.
- buying and selling trades should be logged in separate tables.
- can only have up to a total of 3 entries and 3 exits within a single trade
- can both have long (buying stock as an entry trade and selling to close) and shorting (selling stock as entry trade and buying to close).
*short trades should have negative shares, as shown in the image below, for both entries and exits (for later calculations convenience).
Challenges:
- after buying a stock, I might increment or decrement my shares of stock later on after buying and/or selling different stocks, or after buying or selling the stock thereof.
- after selling all my shares from a single stock, I might open a new position and buy the same stock multiple times immediately after or later on after many different trades from different stocks have been executed.
- buying and selling stock(s) may occur within the same day.
I am working with MS Office 2019 but would prefer a solution working with earlier ms version (no earlier than 2016), and avoiding vba as much as possible.
I would deeply appreciate it if anyone would be of any help to my project. Thank you in advance.
Bookmarks