R/SQL - Portfolio Performance

Question

Suppose I have a table: 3 portfolio accounts, date, and %change, i.e., the 5 actually stands for 5%.

Date      Account   %Change
8/1/2015    A          5
8/1/2015    B          1
8/1/2015    C          2
8/2/2015    A         -2
8/2/2015    B          6
8/2/2015    C          1
8/3/2015    A          3
8/3/2015    B         -4.5
8/3/2015    C         -3

Now I want to add a new column "Score", and automate the process of calculating the score based on %change, let's say 8/1/2015 is the earliest data, then the score for account A on 8/1 will be 100*(1+5%)=105, on 8/2 will be 105*(1-2%)=102.9, basically it's like today's score = yesterday's score * (1 + %change). Let 100 be the base score before the earliest date.

Date      Account     %Change      Score
8/1/2015    A           5           105
8/1/2015    B           1           101
8/1/2015    C           2           102
8/2/2015    A           -2          102.9
8/2/2015    B           6           107.06
8/2/2015    C           1           103.02
8/3/2015    A           3           105.99
8/3/2015    B          -4.5         102.24
8/3/2015    C          -3           99.93

I have two problems here:

1. How to convert %change column to actual percentage type, like 5 -> 5% or 0.05
2. Date + Account together is like primary key, so the score is based on different accounts over the time. We can't not simply use last row to calculate current row because the account from last row is different from current row. It might be easier to sort the data based on account, but we still need to check which row is the new account after we sort the data, for example, row 1-3 -> A, row 4-6 ->B.

After sorting:

Date        Account     %Change   Score
8/1/2015        A          5       105
8/2/2015        A         -2       102.9
8/3/2015        A          3       105.99
8/1/2015        B          1       101
8/2/2015        B          6       107.06
8/3/2015        B         -4.5     102.24
8/1/2015        C          2       102
8/2/2015        C          1       103.02
8/3/2015        C         -3       99.93

The original data source is in SQLServer. This is the data pre-processing, after I get the expected data format, then I will use Tableau for data viz. Basically the whole purpose is to visualize the data, but original data doesn't have "score", it only has %change. It seems Tableau is not good for this kind of data manipulation, so I am wondering if I can do it within SQLServer or use R for calculation.

If you have any ideas or suggestions, post it, thanks!

Answer 1

To convert the values in column C to percentages, merely divide by 100. You can do this by

• Enter 100 in some blank cell.
• Edit/Copy
• Select C2:Cn
• Edit/Paste Special/Divide
• ESC to remove the selections
• You may want to format as Percent with 1 decimal, as I did in the screenshot below.

Assuming the 108.15 for the third instance of A is an error, and that your table begins in A1 with the column headers in row 1, then a formula that will give you the results you show (and it doesn't matter if the data is sorted or not, would be:

D2: =IF(COUNTIF($B$2:B2,B2)=1,100*(1+C2),LOOKUP(2,1/($B$1:B1=B2),$D$1:D1)*(1+C2))

Formula Explanation