I've a table which shows performance of stocks. In 1st column there's list of stocks and in the 2nd column performance of each of the stocks. Now is need to add a third column which represents the performance of the benchmark which is S&P500. The tricky part is when I add the third column for S&P 500 returns it needs to be static i.e. the return of S&P500 will be the same number repeated for each of the rows irrespective of the corresponding list of stocks in each of the rows.
Please look at the attached image and kindly help if you can?
How do you get the benchmark figure? I guess you need to have the benchmark fluctuating also over the days you load the dashboard.
If you have the figure readily available somewhere, you could load it, define a variable with the value, and then insert it into your load script for the final table. That way, you each time load the 'new' benchmark, and add it to the table.
Solved this yet Dave? Per Johan's comments it does depend on how you have connected/loaded the data. For example if your table is supposed to fluctuate with time and you have multiple periods of time in your analysis then it's a mapping solution. I have done something similar where I look at the monthly SP500 returns. If yours is simply a single point in time you could map your data this way : Country -> Industry -> Sector -> Stock -> Stock Return %.... then I have another table related to various Index returns - it has fields Country -> Index -> Index Return %..... because they join on the country field that means when you select USA your stocks would show their returns and and the last column would have index returns against each stock... that way you could then do what I would which is calculate the excess returns in the next column then have a field to calculate average return, std deviation and thus the Sharpe Ratio... let me know if you want an example of this