Dynamic Running Total - Calculating Shares Outstanding

[takumis]

Hi Alteryx Community,

I'm trying to calculate a running total of the shares outstanding after stock splits and stock conversions. I'm having difficulty coming up with a workflow that would accommodate any number of share classes, stock splits and stock conversions.

My assumption would be that I need to create running totals that essentially reset when a stock split or stock conversion occurs. However, I've hit a wall in trying to figure out how to build a workflow that does that while being flexible to incoming data. 

Table 1: Stock Activity

Date Issued	Share Class	Amount of Shares Issued
1/1/2001	Common A	5000
1/1/2002	Common A	5000
1/1/2003	Preferred A	1,000
1/1/2004	Preferred B	3,000

Table 2: Stock Split

Date	Share Class	Ratio (New Shares over Old shares)
6/30/2001	Common A	2
6/30/2003	Preferred A	2

Table 3: Stock Conversion

Date	New Share Class	Old Share Class	Ratio (New Shares over Old Shares)
3/15/2004	Common A	Preferred A	1
3/15/2004	Common A	Preferred B	2

Desired Output:

Date	Running Total_Common A	Running Total_Preferred A	Running Total_Preferred B
1/1/2001	5,000	0	0
6/30/2001	10,000 (5,000 x 2)	0	0
1/1/2002	15,000 (10,000 + 5,000)	0	0
1/1/2003	15,000	1,000	0
6/30/2003	15,000	2,000 (1,000 x 2)	0
1/1/2004	15,000	2,000	3,000
3/15/2004	23,000 (15,000 + 2,000 + 6,000)	0	0

[jdunkerley79]

That's fiddly due to conversions.

I suggest something like:

- Put all inputs into one table with common headers (Date, Share Class, Issued, Ratio, Old Share Class). Mapping New Share Class to Old I think

- Next Sort by Date then Share Class

- You can then build an iterative macro with following logic:

1. Feed a starting point of 0 as of 1/1/1900 for each class

2. Select rows up to next conversion and use multi row to apply these

3. For first conversion apply conversion first to New and then to Old

4. Feed back end positions after 3 into next iteration.

I had a quick play but it will take some time to make work I think. Will try and put together later today.

[jdunkerley79 · Accepted solution]

Finally got something to work. Keeping it generic makes it quite complex.

First, make an ordered list of all the events.

Then create a starting position like:

Share Class	Issued	RecordID
Common A	0	-1
Preferred A	0	-1
Preferred B	0	-1

This is the table which the iterative macro will keep updating.

The iterative macro is pretty fiddly as well, unfortunately:

The first section picks the rows that haven't yet been processed by the iterative macro. It then chooses down until the first conversion.

Next, it processes the splits and issues using a multi-row formula

After this, it processes the first conversion by getting the current issued state of all stocks and converting as needed. 

If there is no conversion left the iteration will stop otherwise it feeds the final positions back in.

A fun and interesting problem - I hope this is enough to get you started.