M/A calculation

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
greg
Posts: 68
Joined: Mon Aug 10, 2009 10:17 am

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?
greg
Posts: 68
Joined: Mon Aug 10, 2009 10:17 am

I guess I didn't explain my problem very well or this is a stupid question?
tumby
Posts: 32
Joined: Wed Mar 13, 2019 6:49 pm

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.
greg
Posts: 68
Joined: Mon Aug 10, 2009 10:17 am

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?
greg
Posts: 68
Joined: Mon Aug 10, 2009 10:17 am

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
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

greg wrote:
Sun May 09, 2021 9:32 pm
do I have to Average 5 cells or do I have to sample every 5 row to get the results I'm after?
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.
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”