Excel Help

[bmann13]

Hi All,

Can any kind soul who knows a thing or two about excel expalin in layman terms how I can display a live chart as shown here:

http://www.betangel.com/tutorials/Ladde ... rview.html

I know how to get data into excel using BA, but not how to display a live chart of any selection I choose to chart.

Thanking you in advance.

Bob

[bmann13]

I'm quite amazed that no one knows how to display live charts on excel, or even point me to the right direction. Oh well, if I find out I will let you all know.

[GeorgeUK]

Sorry mate - i missed your original question.

Never tried it myself, but for excel to display a chart, it must have the full list of data you want to see.
What i would suggest is a macro that records the price for a horse, adds the next price change to the end of that etc and after the price is updated, display the chart.

pseudo-code would be something like:
screenupdating = false
delete chart (if there is one)
copy odds of horse to rightmost column on sheet2 (same row as horse is on)
chart data to use = rightmostcolumn to leftmost (unless columns used > 20 then use rightmost - 20)
display chart
screenupdating = true

As i said, never tried it, but the only way i can think of doing it is using a macro to record the data, then display the chart from that data. Limiting the columns displayed in the chart is just so you can see the rises and falls - otherwise you could end up with a huge chart that looks like a straight line.

Hope this helps

[bmann13]

Thanks GeorgeUK.

Gulp!

Macro...pseudo-code....programming...

I haven't got a clue what you are talking about

Sorry to sound like a dumba@$, the best I can do is import the data into excel...after that it is all a blur When you got time could you please break it down into tiny baby steps. I really appreciate your help.

[Ian]

You can log individual prices so you don't need a macro to do that part. You should just be able to chart the output as normal I would imagine - not that I have ever tried to do it.

[bmann13]

GeorgeUk,

I had a quick look at your reply on macros on another thread in this forum. I did as you said and I got a chart up...my first chart
The only problem with the chart now is I only get a dot that moves up or down as prices are update via BT.
How do I get it to plot a line chart?

Ian,

Thanks for the input. BT does not allow to log individual prices at the moment so I would not know how to log individual prices/

[GeorgeUK]

Not got time to look at it - at work.

The dot will be moving up and down because that is the price it is looking for. You need to record the first price, then move across 1 cell and capture the 2nd price, then move along and capture the 3rd etc
Then your graph will look at not only 1 cell, but the last x cells you have recorded.

I'll get back to you later.

[Ian]

I didn't realise that the exporting of individual odds had been disabled in the API version. It still works on the pre-API version (0.99v).

[bmann13]

Hi GeorgeUK,

Did you get a chance to have a look at my problem?

[GeorgeUK]

Part way there.

Done some code to track what has been happening with a selction - keeping the data on sheet2 while BA updates sheet1 as usual
But having a bit of trouble with the chart as it keeps giving me data from sheet1, which is not what we want.

Working on it though

[GeorgeUK]

You'll need to play around with it - or someone else. I'll have a look again tomorrow, but it's the way i've got the dynamic part of the data that is causing a problem. There are no markets inplay for me to test on.

when you get your workbook open, set up BA as usual and have the data going to sheet1
copy A5 to B30 to sheet2 A5 - we need a figure in column B - this can be fixed later with a workbook open event or something similar

Open the VB Editor
Doubleclick sheet1 (so you can put in the code)

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True

Dim UserWants As Range
Dim chartarea As Range
Dim x As Range
   
Set UserWants = ActiveCell
Set chartarea = Sheets("Sheet2").Range("A" & UserWants.Row)
Set x = chartarea.Offset(0, 25)

Dim cht As Excel.Chart
Set cht = Charts.Add

With cht
    .ChartType = xlLine
    .SetSourceData Source:=Sheets("Sheet2").Range(chartarea.Address, x.Address), PlotBy:= _
    xlRows
    .Location Where:=xlLocationAsObject, Name:="Sheet1"
       

End With

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastrow
Dim CopyingTo As Range

Set lastrow = Sheets("Sheet2").Range("A65536").End(xlUp)
Set CopyingTo = Sheets("Sheet2").Range("A5:A" & lastrow.Row)

Range(Range("F5"), Range("F65536").End(xlUp)).Copy _
Destination:=CopyingTo.End(xlToRight).Offset(0, 1)

    If CopyingTo.End(xlToRight).Offset(0, 1).Column > 20 Then
        CopyingTo.Offset(0, 1).Delete Shift:=xlToLeft
    End If


End Sub

What should happen:
Each time sheet1 is updated by BA, the data will be transferred to sheet2 until there are about 20 entries.
After that, the 21st entr will be put in and the 1st removed.
The problem i have is that when the chart is updating after this, it is decreasing by 1 each time until what was the 20th entry gets deleted and you're left with a blank chart.
I set it up so you just doubleclick the horse name or whatever to bring up the chart.

Sorry it's nots working - never used charts before. (And never intend to ever again )

[bmann13]

GeorgeUk,

Thank you for taking the time and effort to help me I really appreciate it. There is nothing to be sorry about, we have a base to work from now, correct that...you have a base to work from...I'm just the monkey trying to make sense of all this

I do see what you are saying about the disappearing prices. Hopefully we can get it fixed.

[GeorgeUK]

Had an idea about that - we might be able to use the "usedrange" for a particular row so that it increases from the start up to 20ish, then there is still 20 after a cell gets deleted. Will work on it though.

I'll edit this post later - note to self type thing

Busy at work today - don't seem to have got enough done yesterday

[excel_toy]

Just had a look at the bet angel vid - do you mean you want the betfair graphs ?

The same which are displayed when you click a runner's name on the betfair site ?

[bmann13]

Hi excel_toy,

No, I want the graph of the live odds trading on betfair with the voume just like in the bet angel vid. What I am trying to do is to see when big volume comes in the market and in which way it moves the odds. Normally I have found the odds will move in that direction for a few ticks, enough for me to get in and out of a trade with a profit or breakeven at worst. If Gary can implement trailing stoploss into the ladder we could ride the market if it becomes a drifter or steamer.

Let me know if you need more info.