Report guidance

CNH

Hi, I have this week downloaded Portfolio Slicer and also Power BI. So I am brand new to both. What I have seen of your work and the software I can see great advantages for me.

I have been running my portfolios in excel and have been extracting performance / tracking from this data base. I had not found other commercial solutions that were able to emulate my objectives. So having seen what you have prepared I am excited about this as an improvement to my process. I have entered some of my earlier data and after some toing and froing I am comfortable with this aspect.

In your PortfolioSlicerC2.3 power BI I see in the reports file that you have a "Profit" [Profit = Report[Dividends] + Report[Capital Gain]]

I am trying to use this data to create a visualization to simulate what I have done in excel. Examples are in the attached pdf files After a few attempts I am not achieving an outcome.

Are you able to guide me here. I have tried copying and then modifying the visulaisation on your "Daily Movment" tab which is showing "Total value of investment" - but withoutr luck.

Let me know if this is too big an ask. I know that I could spend a day or 2 and possibly not get where I need to - but possibly then throw my hands up and say it is too hard.

VidasMatelis

Hi,

What you are trying to do requires one more Date dimension Date2 that is not in current structure. You need that extra Date2 dimension to specify from when to start calculating accumulated profit.
It would be fairly simply to add calculation for accumulated profit from the start of your data. For that you would just create new DAX calculation:

Profit from Start = CALCULATE(Report[Profit], DATESBETWEEN('Dates'[Date], BLANK(), LASTDATE('Dates'[Date])))

As you can see in DATESBETWEEN function second argument for Start date is Blank() - that means - start of the Date dimension. If you would have Dates2 dimension, instead of the Blank() you would use Dates[Date] value calculated based on Dates2 selection.

If you know PowerBI or PowerPivot and DAX, then you could create what you want with DAX and small structural change.

One more option that you have - create simple PivotTable with Monthly profit, then use Excel functions/Pivots to create your reports out of that PivotTable results.

CNH

Hi Vidalis,

I have made head way. Can you advise whether the data is stored on a daily basis or is it monthly other than in the past month or so.

I see that the quotes are in two files, e.g _A2M.AX_ - this file has daily from Jan 1, 2018 but prior to that the quote is saved on a monthly basis.

The second file _A2M.AX__Archive has data on a daily basis. So I am presuming the excel worksheet is accessing only the "_A2M.AX_" file.

If possible I would like to access the daily movements - what are the implications here?

VidasMatelis

Portfolio Slicer uses daily quotes for last 40 days and monthly quotes before that. This is done just for performance reasons - less quotes makes refresh faster. So, by default, when file quotes.csv is created you have daily quotes for last 40 days and monthly before that.

But Portfolio Slicer scripts save daily quotes, so it is very easy to switch to load all daily quotes. Simply in psConfig.txt change parameter IncludeQuoteArchiveFolder value from "No" to "Yes" and recreate quotes.csv (run .bat file) and then refresh Excel - and you will have all daily quotes.

As I said implication - just slightly longer refresh time. Test refresh time before parameter change and after and you will know if impact is big.

CNH

Hi, I have made the change to psConfig.txt. The refresh time is not an issue. I will be mindful as I go forward with populating my history. Thanks for your guidance

CNH

Hi Vidas, I am continuing to work my way through the visualizations that I would like to have in my Power BI and I am now back to the visualization that I initially raised in this thread. Refer to file Accumulated profit by year (1).pdf reattached.

For the actual profit I have used the following code:

*Profit YTD = TOTALYTD([Profit], 'Dates'[Date], "30 June 2018" )

I am now trying to introduce my Profit targets. What I have done is introduced a worksheet "ProfitTarget" in to PortfolioSlicer excel worksheet.

I have the taken this into PowerBI

From there I have tried various processes, but essentially I have no idea how to achive the outcome of have my Targets overlaid on my * Profit YTD [3 years] tab.

Attached to this post are example data in the excel model that I am using with example data.

I wasn't able to attach the PowerBI file, but the attached link should give you access to it

https://1drv.ms/u/s!ArtOMe8OWNmVgwZ2mZTfQE1X7QGM

As I said above the excel file is your current version 2.4.04 .

You will note in PowerBI that I have a number of additional visualisations, these are place in front of your standard visualisations and the tab highlighted with * [this will enable me to identify any visualisations to take across to future versions of PowerBI. Modified measures/calculations in this file are also highlighted with an *

Are you able to guide me on how to introduce Target 1 and Target 2 to my * Profit YTD [3 years] tab in PowerBI.

Thank you.

VidasMatelis

CNH - I do not understand what you want to do. How do you want to calculate Profit Target? Currently there are formulas that calculate Profit Target based on another symbol - "Expd REturn % (Alloc)" and "Expd Return % (TMTR) - can you use similar logic?

CNH

Vidas,

The profit target is an "aspirational" $ target that I am setting at the beginning of the year. If you look at the excel spreadsheet I have established 3 targets, one conservative, one aggressive and one mid point. These are detailed numerically by date in worksheet "PortfolioTarget" in PortfolioSlicerV2.4-XL16.xls.

This target is not determined by index returns, but more a desire to achieve a 8% ROR on a conservative basis, or 12% on an aggressive basisi. [10% being a mid point]. In my review I would also be factoring actual market performance [but I am not trying to show this on this visualisation]

So what I am trying to show is if, on a day to day visualisation, whether I am meeting this $ aspiration.

Having said that, it is a personal vision for my portfolio.

So in short is there a way to take this dataset from Portfolio Slicer [the data set in the new worksheet that I have added to PortfolioSlicerV2.4-XL16.xls] and display it on my Portfolio BI tab "*Profit YTD [3 Years]" which is currently showing the profit as defined by measure"*Profit YTD"

As I have said this is something that I have on my old portfolio excel monitoring spreadsheet that I am hoping to now be able to do on your Portfolio Slicer.

CNH

Hi Vidas,

I have now achieved the outcome I am after.


One of the issues I had was I was using a line chart, I have changed this to an area chart and with new measures defined all seems good.

I will look further at this tomorrow with actual data rather than sample data and see whether my expected outcomes have been achieved.