Can DLookup be used together

[shakya69]

My project involves getting market data using the Yahoo finance API. I am trying to calculate the exponential moving average, where the current average calculation depends on the average from the day before. I think i can use Dlookup to find the EMA but i am unsure if this is the rout i need to be taking.



Thank you

[John_G]

You'll have to give us a lot more information than that; where are you doing this (a form?), how do you want to use it, etc.

However, just as a guess, if you are trying to create a calculated field in a table (which I strongly suggest you NOT do), then DLookup probably cannot be used. The list of calculations that can be used in a calculated field is very limited.

[shakya69]

Sorry for the incomplete post. I am trying to calculate the exponential moving average using expression builder. Are there any simple methods to calculate EMA's using sub forms? Am thankful for all your help.

[orange]

Is that calculation available from yahoo finance directly?
If not, can you show us the calculation using some sample data?

I see 50 day and 200 day moving average.

[shakya69]

@Orange,
No, From Yahoo finance we get the High open low close data. When done in excel we would just use the basic Exponential Moving average formula.

(Closing Price)(2/(t+1))+EMA(n-1)(1-(2/(t+1)))

t=Time in integrals of days

So it is difficult to understand how to retrieve a previous value when none of the data is flat.

[orange]

Based on a little googling (http://stockcharts.com/school/doku.php?id=chart_school:technical_indicators:movi ng_averages)

Exponential Moving Average Calculation

Exponential moving averages reduce the lag by applying more weight to recent prices. The weighting applied to the most recent price depends on the number of periods in the moving average. There are three steps to calculating an exponential moving average. First, calculate the simple moving average. An exponential moving average (EMA) has to start somewhere so a simple moving average is used as the previous period's EMA in the first calculation. Second, calculate the weighting multiplier. Third, calculate the exponential moving average. The formula below is for a 10-day EMA.



SMA: 10 period sum / 10
Multiplier: (2 / (Time periods + 1) ) = (2 / (10 + 1) ) = 0.1818 (18.18%)
EMA: {Close - EMA(previous day)} x multiplier + EMA(previous day).

A 10-period exponential moving average applies an 18.18% weighting to the most recent price. A 10-period EMA can also be called an 18.18% EMA. A 20-period EMA applies a 9.52% weighing to the most recent price (2/(20+1) = .0952). Notice that the weighting for the shorter time period is more than the weighting for the longer time period. In fact, the weighting drops by half every time the moving average period doubles.

If you want to us a specific percentage for an EMA, you can use this formula to convert it to time periods and then enter that value as the EMA's parameter:

Time Period = (2 / Percentage) - 1

3% Example: Time Period = (2 / 0.03) - 1 = 65.67 time periods
--------------------------------------
Seems you should be able to create the calculation, but you will need to ensure you have sufficient time periods.
That is some historical data, so you get your simple moving average, then your multiplier etc. I haven't tried it, but it seems
"doable".

[shakya69]

@Orange
So, how do I do any of that in access forms?

The problem I am having is how to correctly do this calculations and display the output within the form.

Thank you.

[John_G]

If I may jump in here -

I did a quick Google search last night using "calculate moving average in ms access". I found a number of interesting and relevant items, some with code. All of them required VBA, some use arrays. A common approach was to write a user-defined-function, and then use that function in a query.

The upshot of it is that your requirement is quite doable with a little code.