Forum Discussion

chrisB13's avatar
chrisB13
Frequent Visitor
7 years ago
Solved

Historical Stock Data Concept

Hi Everyone,

 

I am currently working on building out a Historical Stock Market analysis. 

Currently I am pulling in a parametered function that brings in the entire history of each of the symbols I am looking for.  This is great (Obviously at some point I will need to find a more cost-efficient solution, but for now this is a good concept).

I have all the data pulled in and I am looking to calculate Simple Moving Averages
For those of you unfamiliar this is simple just (SUM(Close Price of the STock)) / Amount of days)

Example and I will show you what I am running into :   

Date     -   Close Price

4/17/19 - $11

4/18/19 - $11

4/19/19 -   $10
4/20/19 - (No Data -- Stock Market Closed)

4/21/19 - (No Data -- Stock Market Closed)

4/22/19 - $12

 

The 4 day moving average for 4/22 should include 4/22, 4/19, 4/18, 4/17

AND NOT include 4/20, 4/21

So:

($11 + $11 + 10 + 12) / 4 (days) = $44/4 = $11 4 day Simple Moving Average

And not:

($10 + No Data + No Data + $12)/ 4 (days) = $22/4 = $5.50 4 day Simple Moving Average

 

Here is my calculation for this:

4 Day Simple Moving Avg = DIVIDE(
    CALCULATE(SUM(TickerList[Close_Price]),
    DATESINPERIOD(TickerList[Date],
    LASTDATE(TickerList[Date]), -4, DAY) ), 4, 0)


The problem is that LASTDATE() appears to be pulling in 4/20 and 4/21 even though those days dont exist.
Below you can see what this 4 day moving average is calculating as
Keep in mind I am using Close_Price column to calculate the average, also notice the dates missing in the column
This is not a mistake, as mentioned above -- there isn't price data for those dates because the market is closed.
The last screenshot is just an overview of what I am working with, again this is STRICTLY a concept and by no means am I claiming this looks nice yet :).
 
Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi chrisB13,

    Please see the below.

    4 Day Moving Avg =
    VAR __maxDateInCurrentSelection =
        MAX ( TickerList[Date] )
    VAR __top4DaysWithValues =
        TOPN (
            4,
            FILTER ( ALL ( TickerList ), TickerList[Date] <= __maxDateInCurrentSelection  ),
            TickerList[Date], DESC
        )
    RETURN
        AVERAGEX ( __top4DaysWithValues , TickerList[Close_Price] )

     

    Hope this helps.
    Mariusz




  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi chrisB13 ,

    Try the below and let me know if it performs any better.

    4 Day Simple Moving Avg = 
VAR d = MAX(TickerList[Date])
VAR t = TOPN(
    4, 
    FILTER(
        ALL(TickerList[Date]),
        TickerList[Date] <= d), 
    TickerList[Date], 
    DESC
)
RETURN 
AVERAGEX(t, CALCULATE(SUM(TickerList[Close_Price])))

    Hope this helps
    Mariusz

     

5 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi chrisB13,

    Please see the below.

    4 Day Moving Avg =
    VAR __maxDateInCurrentSelection =
        MAX ( TickerList[Date] )
    VAR __top4DaysWithValues =
        TOPN (
            4,
            FILTER ( ALL ( TickerList ), TickerList[Date] <= __maxDateInCurrentSelection  ),
            TickerList[Date], DESC
        )
    RETURN
        AVERAGEX ( __top4DaysWithValues , TickerList[Close_Price] )

     

    Hope this helps.
    Mariusz




    • chrisB13's avatar
      chrisB13
      Frequent Visitor
      7 years ago

      Thank you, slightly worried about the performance of it though.   Looks like it hits performance pretty heavily, even trying a 3 day moving average is taking a few minutes to load.

      • chrisB13's avatar
        chrisB13
        Frequent Visitor
        7 years ago

        It actually failed to load, ran out of RAM (32 GB)

         

        Not sure why, but I would have to assume it has to do with the ORDER BY portion of the DAX.   If we have 3000-4000 rows, it will need to do each row individually and forced to rebuild the Descending order every time.