Historical Stock Data Concept

[chrisB13]

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 :).

[Mariusz · Accepted solution]

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 · Accepted solution]

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]

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]

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.

[Mariusz · Accepted solution]

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

[chrisB13]

Thank you, that works much quicker!!

I may be coming back on here for some other calculations, that was the easiest of the calculations so far.

RSI, Stochastics, MACD, Variance, etc... all need to be done