Graphing on a MAC With VBA

[CPerry]

Hi there,

I am very new to this so please bare with me if I sound stupid. I am running Microsoft Excel for Mac Version 15.12.3 (150724) and require some help in programming a VBA code to do something rather specific. I know how to open the VBA window and that's about it. Additionally to answering this question, if anybody could point me in the right direction to learn how to do this kind of stuff from home I would really appreciate it

Firstly the purpose. Basically I am looking to programme some trades via a spreadsheet and I would like to create a graph of the price moves every second on a live market. I have the data coming in every 200m/s and I would like a way of recording the 'price' at every interval just going on and on for 10 minute periods (I will then create a graph using this back catalogue of data). This is for horse racing so I will need 6 loops happening simultaneously.

For sake of simplicity, I have the following temporary placed cells: A1 (Current Time), A2 (Time until start), A3 (Volume), A4 (Open), A5 (High), A6 (Low), A7 (Close). A8 onwards will be the same (Volume, Open, High, Low, Close) for the other 5 horses, so therefore there will be approx 30 cells that will each need to be refreshed every 200m/s.
Can anybody help me out with a VBA code that automatically starts 10 minutes before the race and records the price for all of the cells in the A row every time the volume cells change (which are being refreshed at 200 m/s) placing each number a single cell below the previous for 10 minutes.

Again, I massively appreciate anyone taking the time to look over this for me and I eagerly await your reply.
Cheers,

Callum.

[Leith Ross]

Hello CPerry,

You should change your title to something more informative like "Real Time Graphing of Trades using VBA on a Mac".

[CPerry]

Thank you for your reply Leith.

[offthelip]

You are trying to create a real time system. There are a lot of things to consider when doing this:
1: Have you done anything like this before? what about trying something simple like doing it with just one data item first
2: how is the data arriving in the spreadsheet, will the "arrival" trigger an EXCEL event?
3: do you need to copy it from the "arrival" location to somewhere else to draw the graph,
4: how are you going to trigger the act of copying the data,
5: how much data are you getting each second and how fast is your computer. will it complete all the computation within 200ms
6: How often do you need to draw the graph?
7: How are you going to keep the graph just looking at the current data, i.e are you going shuffle up the data or are you going to keep moving the graph down the data.
8:how do you trigger an update to redraw the graph,
9: What will the latency of the system be and is that acceptable?



Note: what you are trying to do is quite possible in EXCEL VBA and I have done something similar with stock prices, but I must warn you it is not easy!!

[CPerry]

You are trying to create a real time system. There are a lot of things to consider when doing this:
1: Have you done anything like this before? what about trying something simple like doing it with just one data item first
2: how is the data arriving in the spreadsheet, will the "arrival" trigger an EXCEL event?
3: do you need to copy it from the "arrival" location to somewhere else to draw the graph,
4: how are you going to trigger the act of copying the data,
5: how much data are you getting each second and how fast is your computer. will it complete all the computation within 200ms
6: How often do you need to draw the graph?
7: How are you going to keep the graph just looking at the current data, i.e are you going shuffle up the data or are you going to keep moving the graph down the data.
8:how do you trigger an update to redraw the graph,
9: What will the latency of the system be and is that acceptable?



Note: what you are trying to do is quite possible in EXCEL VBA and I have done something similar with stock prices, but I must warn you it is not easy!!

Firstly thank you for the detailed reply.
1) I am quite competent with excel formulas and graphs but the realm of VBA is a completely new challenge for me so yes I think I will just start out with running one horse at a time so just looping A1-A6.
2) The spreadsheet runs through a programme called BetAngel, on there you can change how often the data gets fed through. I was thinking of using a timed trigger so that every time the volume changes (which would be every 200m/s because the markets are mega fast) the cells would all shift one position allowing the data to flow.
3) The graph problems I will work around next. I can do plenty of commands with just the data alone so the graphs I am in no rush to get working if they prove difficult.
4) No idea, any suggestions?
5) I can alter the speed although I'd like not to go above a 1 second refresh otherwise I lose my edge in the market. If things are slow I am prepared to update my hardware in due course.
6) The graph I was thinking of updating each second (so just selecting every 5th cell on a 200m/s default set up)
7) I was thinking of drawing the graph only showing the previous 30 seconds so as data shifts past this range it is simply not shown visually but I can still use it for buy/sell commands and other clever formulas as I come up with them.
8) Again, any suggestions how to best refresh the graph without overloading my system?
9) I need to experiment with the latency of my systems in all honesty. My systems are merely theory at the moment but I'm looking forward to the challenge.

You've given me a lot to think about and laying it out like this has helped clear my thoughts/plans already. If you or anybody else reading this can give me advice and/or mock up a few lines of vba to get me started I would really appreciate it.

Cheers,

Callum.

[offthelip]

I am not familar with Betangel but I assume it will write updates straight into you worksheet. Hopefully that will trigger a worksheet change event. ( if not you will have to use a timer) but asssuming it does , the following code will copy the new data to the next available line and update data index.
I recommend having the data run down you spreadsheet rather than accross because you can see more rows of data than columns of data. However the same technique can be used to write it across the sheet if you want.
Also I recommend just appending the new data to the existing data for two reasons firstly it minimises the number of cells you need to update and thus the time taken. secondly you say you want to preserve the data for historic analysis.
The way to solve the graphing problem where you want to only look at the latest data is to create another table which uses the index ( In I2 in my example to pick up the latest dat ausing the offset function:

e.g
=OFFSET(A4,I$2-15,0)
=OFFSET(A4,I$2-14,0)
=OFFSET(A4,I$2-13,0)
=OFFSET(A4,I$2-12,0)
etc in successive cells down your spreadsheet

code to do the copying , you need to put this in the worksheet change event for the worksheet with the updating data
I assume the new data is arring in the ragne A2 to G2

Private Sub Worksheet_Change(ByVal Target As Range)If Not (Intersect(Target, Range("A2:G2")) Is Nothing) Then
  ' copy a1 to g1 to last row
   Application.EnableEvents = False ' you must do this to prevent excel going into a loop
   inarr = Range("a2:g2   ").Value ' pick up new data
   ind = Cells(2, 9) ' pick up the index for the next location
   Range(Cells(ind, 1), Cells(ind, 7)) = inarr 'write the data to a new line
   Cells(2, 9) = ind + 1 ' increment the index


End If
   Application.EnableEvents = True ' you must do this to ensure excel keeps working properly
   
End Sub

[CPerry]

I have made a slight adjustment to the sheet as the data is now starting at C39:J39 instead of A2:G2 for a few reasons (graphing, triggers, neatness etc) so I copied those instructions and just amended the top line to show:

Private Sub Worksheet_Change(ByVal Target As Range)If Not (Intersect(Target, Range("C39:J39")) Is Nothing) Then


But once I click off of it, it shows
"Compile error:
Expected: end of statement"

Even when I left it as A2:G2 it did the same. Also the "IF" is highlighted, as I highlighted above in bold, so it assumes the problem is around there. Any ideas what's happening?

[mdmackillop]

There is an issue with the code formatting above in that the first two lines are being joined

Private Sub Worksheet_Change(ByVal Target As Range)

If Not (Intersect(Target, Range("A2:G2")) Is Nothing) Then

[offthelip]

Thanks MD
Apologies, I didn't spot that when I cut and pasted from the vba. Obviously the original had the cr/lf

[mdmackillop]

I've noticed this on a lot of code posts and asked Admin if it can be fixed.