›

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Register

Quick Links

Categories

Tracking weekly movements

pirriperpirriper
in Excel Workbook
Hi

I would like to be able to track weekly movements in a new tab. Could you please advise the best way to do this?

thanks
P

Comments

  • VidasMatelisVidasMatelis
    Hi,

    This would be possible if:
    1. You have Excel version with FULL PowerPivot support. That is you should see PowerPivot menu option. If you are using Excel 2010, then you have full PowerPivot support. If you are using Excel 2013/2016, then just some Excel versions have full PowerPivot support.
    2. You are willing to learn how to add new calculated column "Week" to Date table. I can write the formula for that column, but you will need to add it (based on my instructions).
    3. You know how to work with Excel Pivot Tables, so you can now create reports based on the new Week column.

    If above sounds OK to you, let me know here what Excel version you are using and I'll send you instructions (it might take me few days to do that).
  • pirriperpirriper
    Hi Vidas

    That sounds good to me. PS has been extremely useful for my trading and the ability to track weekly movements will definitely add to this.

    I am currently using Excel 2013 with a PowerPivot menu option.

    Would appreciate if you could send through instructions on what to do.

    Cheers
    P
  • VidasMatelisVidasMatelis
    edited November 2016
    Hi P,

    Here are steps on how to introduce Weekly movements into Portfolio Slicer on Excel 2016. For Excel 2013 this should be very similar.

    1. Create PortfolioSlicer (PS) workbook copy in case something goes wrong and you want to reverse back.
    2. Open PS workbook, go to menu PowerPivot and the click button "Manage". New "Power PIvot for Excel" window will open.
    3. Choose table "Dates" (tab at the bottom). Go to very right where last column is "Add Column". Select that column and then in formula bar add formula:
    =CONCATENATE("Wk of ", IF(DATEADD(Dates[Date],(-1)*WEEKDAY(Dates[Date],2)+1,day) = BLANK(), FORMAT(MIN(Dates[Date]),"yyyy-MM-dd"), FORMAT(DATEADD(Dates[Date],(-1)*WEEKDAY(Dates[Date],2)+1,day),"yyyy-MM-dd")))
    Rename that column to "Week". Keep in mind, that if you will move your cursor to another window (like to copy formula from web page), your Excel workbook will refresh and it will appear that your Excel workbook frozen. Be patient, wait till refresh finishes.
    4. Repeat steps again but this time add formula:
    =CALCULATE(COUNTX(VALUES(Dates[Week]),1), Dates[Week] < EARLIER(Dates[Week]), All(Dates))+0
    Rename this column to "WeekNo" and hide column (Right mouse click, "Hide from Client Tools")
    5. Repeat step again and this time add formula(s) for number of weeks you want to see in your report. Example:
    =IF(Dates[WeekNo]>MAX(Dates[WeekNo]) - 2,"Yes","No")
    Rename this column to "Weeks - Last 2".
    Above formula will create column with values "Yes" for last 2 weeks and "No" for all other weeks. This is helpful when creating reports. You will probably want to have columns like "Weeks - Last 4", "Weeks - Last 5", etc. For that just adjust formula and change "- 2" to "- 4", "- 5", etc.
    6. Close PowerPivot window. Again you might have to wait till PowerPivot model refreshes.
    7. Copy for example "Daily" worksheet. Move Pivot table that is below "Daily Portfolio Movements" to the right so that movement report can expand without overlapping. Add filter "Weeks - Last 20" to filter area and select "Yes". Remove "Date" From Rows area and add "Week" into Rows area. Now your report will show weekly values.

    I hope this is good enough for you to start. As I said in this instructions I expect that you know a bit about Pivot tables.

    Regards,
  • VidasMatelisVidasMatelis
    Also, I forgot to add:

    Normaly, PS has daily data for last 30 days and monthly data for dates before that. As you want to track weekly movements, you need to make sure that you provide daily quotes. If you are using quote extract scripts from this website, then in psConfig.txt you should change parameter: 
    
Yes
  • pirriperpirriper
    Thanks Vidas, I've manage to set up a tab which tracks weekly data.

    One question - the formula in Step 3 provides info for the week beginning every Monday. How can i change this to track data for the week ended every Friday.

    Thanks
    P
  • VidasMatelisVidasMatelis
    Hi P.,

    I used DAX function Weekday to pick week. More info about this function here: https://msdn.microsoft.com/en-us/library/ee634550.aspx
    There is no option for week to start or end Friday, there is just an option for week to start Sunday or Monday. You could try to subtract 3 days from Monday to get Friday, but that could introduce other issues at the Year end. So I am not sure how to do this for end of Friday tracking.

Sign In or Register to comment.