Hi All,
I'm trying to recreate my advanced chart settings in a Excel sheet but can't figure out how to include the chart update/ interval setting (1 , 5 or 10 seconds) into the calculation ? When using an advance chart in BA I have a fast 10 period MA and a slow 35 period . My Excel sheet updates every second so I've got 3 columns one with the price then an average of the price at 10 seconds and at 35seconds in the third column which works ok but if I want to recreate the results I'd get from a 5 or 10 second chart how would include these in the calculation please?
M/A calculation
Hi greg
I solely use Excel for all my betting so hope i can help. I am struggling to understand what the problem is from your post, perhaps you could clarify.
Are you sampling a price every second, storing the results so creating a time series and then calculating two MAs from that data, so creating two more time series?
If you are, then why cant you simply sample every 5 seconds instead?
Is it that you wish to keep the exisiting time series and so you need to take your already sampled data and re sample it at a lower frequency?
Sorry for lots of questions but I would like to help but need to understand the problem.
I solely use Excel for all my betting so hope i can help. I am struggling to understand what the problem is from your post, perhaps you could clarify.
Are you sampling a price every second, storing the results so creating a time series and then calculating two MAs from that data, so creating two more time series?
If you are, then why cant you simply sample every 5 seconds instead?
Is it that you wish to keep the exisiting time series and so you need to take your already sampled data and re sample it at a lower frequency?
Sorry for lots of questions but I would like to help but need to understand the problem.
Hi Tumby,
Many thanks for posting a reply , yes I have a countdown clock which ticks down 1 second per row then the price in the column next to it , then the two M/As but when I create a chart from this I get totally different crossovers etc than a chart in BA updating at 5 or 10 seconds.
I've tried updating the price every 5 seconds with the two M/As running off that and also having the price update by the 1 second and multiplying the fast and slow MA by 5 , they get close but not quite the results I see in the BA charts?
Many thanks for posting a reply , yes I have a countdown clock which ticks down 1 second per row then the price in the column next to it , then the two M/As but when I create a chart from this I get totally different crossovers etc than a chart in BA updating at 5 or 10 seconds.
I've tried updating the price every 5 seconds with the two M/As running off that and also having the price update by the 1 second and multiplying the fast and slow MA by 5 , they get close but not quite the results I see in the BA charts?
Hi Tumby
Really sorry if I'm slow to understand but I've only just got to grips with the very basic excel stuff as I'd never really used it before this year.
Do I understand correctly that if I have it set up with the price every second and then the two Ma's then do I have to Average 5 cells or do I have to sample every 5 row to get the results I'm after?
Many thanks Greg
Really sorry if I'm slow to understand but I've only just got to grips with the very basic excel stuff as I'd never really used it before this year.
Do I understand correctly that if I have it set up with the price every second and then the two Ma's then do I have to Average 5 cells or do I have to sample every 5 row to get the results I'm after?
Many thanks Greg
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
A moving average is the average over the previous however many seconds. So if you're storing the price every 1st then
The 5s MA is sum(previous 5)/5
The 10s MA is sum(previous 10)/10
etc
If you're storing the price every 0.2s (ie 5 times a second) then
The 5s MA is sum(previous 25)/25
The 10s MA is sum(previous 50)/50
etc
You need to create a list of the price history (for each selection), and for simplicity you could take the current price and insert it into the top of a price history column every 1s. The 5MA is then the average of the top 5 cells, the 30s MA is the average of the top 30 cells etc. Inserting each new price into the top of a column, so that the rest shuffle down, isn't the most efficient way to do it but it's probably the easiest way and it'll get you something working you can improve on later.