calculate an exponential moving average

[ppcash]

Greetings to all, I would like to know how I can calculate an exponential moving average, I am new to Access and I only know the basics. Thanks

[isladogs]

Welcome to the forum. Nothing like starting with something 'easy' to do!

Suggest you give further explanation and examples in order to help members help you!

[Micron]

You're asking for a formula? Available via Google but you'd need to calculate or also store the SMA and retrieve it from a table. Problem with that is that the general consensus is to not store calculated values. Not my area of expertise, but if the SMA is fixed, might be OK to do that. If it will ever fluctuate, then should not store.

You're asking how you'd do this in Access? Then we have to assume you have all the data to plug in the equation. I'd probably take the vba approach although someone might eventually figure out how to plug the formula into a query. Problem I'd have with that is that it would all have to be on one line - that would be complicated and difficult to troubleshoot IMO.

[CJ_London]

Suggest you provide some example data so responders have something to work with.

In principle it is not difficult to achieve - a simple moving average over say 10 days with no breaks would be something like

SELECT A.FK, A.Date, Sum(B.price)/10 AS SMA
FROM myTable A inner join myTable B ON  A.FK=B.FK AND B.Date>=A.Date-10 and B.Date<=A.Date
GROUP BY A.FK, A.Date
ORDER BY A.FK, A.Date

Obviously if this is share trading and there are no prices for the weekend so the calculation will be off - which is why we need to understand the context and the data

To covert this to a EMA you need to adjust for the smoothing factor (2 here) for today and yesterday

SELECT A.FK, A.Date, (A.Price*2/11) + (sum(B.price)/10*(1-(2/11)) AS EMA
FROM myTable A inner join myTable B ON  A.FK=B.FK AND B.Date>=A.Date-11 and B.Date<A.Date
GROUP BY A.FK, A.Date
ORDER BY A.FK, A.Date

Not checked if the code is working as required, but just to give you an idea - join the same table to itself with non standard joins

[orange]

Further to advice offered so far, here's a link via Google that may help.

[ppcash]

Suggest you provide some example data so responders have something to work with.

In principle it is not difficult to achieve - a simple moving average over say 10 days with no breaks would be something like

SELECT A.FK, A.Date, Sum(B.price)/10 AS SMA
FROM myTable A inner join myTable B ON  A.FK=B.FK AND B.Date>=A.Date-10 and B.Date<=A.Date
GROUP BY A.FK, A.Date
ORDER BY A.FK, A.Date

Obviously if this is share trading and there are no prices for the weekend so the calculation will be off - which is why we need to understand the context and the data

To covert this to a EMA you need to adjust for the smoothing factor (2 here) for today and yesterday

SELECT A.FK, A.Date, (A.Price*2/11) + (sum(B.price)/10*(1-(2/11)) AS EMA
FROM myTable A inner join myTable B ON  A.FK=B.FK AND B.Date>=A.Date-11 and B.Date<A.Date
GROUP BY A.FK, A.Date
ORDER BY A.FK, A.Date

Not checked if the code is working as required, but just to give you an idea - join the same table to itself with non standard joins

My formula:
24/03/2019 Leverage 14/3 to 23/03
25/03/2019 price (25/03)*(2/(1+ema 12))+(previous price (24/03)*(1-2/(1+ema 12)))

[CJ_London]

don't understand your formula, why previous price? surely it is the average of the previous 12 prices

[ppcash]

Calculate ema 12 periods:
First step: calculate the average from day 1 to 12
Second step: closing price of day 13 x multiplier + EMA (previous day, in this case the average of step 1) x (1-multiplier)

[CJ_London]

put some numbers to it from your example data, since as you have described it that is the example I provided