Exporting MANY MARKETS DATA to ONE Excel sheet - this is done!

Hi,
we all know that we can export data to excel via custom cells and formulas.

If these data are general data they can be output to ONE excel sheet, but if there are market data  they have to be directed in the cell_querymarket_... area on every market sheet as it is described in the manual.

I wrote an Excel program (see attached file MFPtoExcel2007.xlsx) that gives the possibility MFP to output many markets data in ONE excel sheet.

Description

ImportFromMF() is a UDF (user defined excel function), via which MFP outputs market data to Excel via a custom cell and a custom formula.
Every (started) market from MyMarkets list will be displayed in Excel sheet1 on the same row as market_index in MyMarkets list. We can output as many data per market as we want, they will be displayed on adjacent cells on sheet1 starting from column 1.
We can even start first and sixth market in MyMarkets list, and they will be displayed at first and sixth row in sheet1.
They will be refreshed according to custom cells and formulas refreshing rules.

How to activate

In MFP settings>Excel options

1. untick all buttons (if you want, it works also with ticked buttons).

2. In custom cell write:
Sheet1!K1 . Excel cell K1 will hold ImportFromMFP() function

3. in the custom formula on the right of the custom cell:
=ImportFromMFP(market_index, market_id, "market_name", s_1_sel_id, "s_1_sel_name", s_1_back_price, s_1_lay_price)

ImportFromMFP() is a user defined function (UDF) in the excel file MFPtoExcel2007.xlsx  that imports data from MFP. The function takes a variable number of parameters  (you can output more/less data from every MFP market to one Excel sheet).
ImportFromMFP() takes at least 2 input parameters

• market_index is not optional and must be number
• any other argument

Notice that, passing textual info (market_name, sel_name) needs to be enclosed in double quotes ("")

In Excel

4. Open Excel file MFPtoExcel2007.xlsx. Do not forget to enable macros in the excel file.

In MFP

5. Launching Excel from MFP (via launch Excel button) on the betting interface

6. Connect it to the already opened MFPtoExcel2007.xlsx

7. Start one or more markets in MFP

E voila!!!

Remarks

1. If we want to export more that 1 selections / market we have to export them as additional function parameters. They will be displayed on the same excel row.

Example:

=ImportFromMFP(market_index, "s_1_sel_name", s_1_back_price, "s_2_sel_name", s_2_back_price)

2. There must be at least one (started) market on MFP in order to refresh

3. If you want to change the cell (K1) where data are imported from excel do the following:

• in custom cells change Sheet1!K1 to new cell(i.e. Sheet1!BA1)
• In Excel, drag & drop cell K1 to new cell BA1, or
• use name manager in excel to change the address of named range "MFPcell"

3. Code inside excel file MFPtoExcel2007.xlsx is fully commented, you can change it to your needs.

You can download the excel file, test it and leave any comments on the forum





best Regards,

Theodore

Well done
I've only had a quick look and can see this could help me speed up excel.

Will look into this more when I get time.

Great work
Cheers

Hi,
did the Excel file worked?
Did you find any problems?

Best Regards,

Theodore

Theodore,

What a marvel - you're a genius - I can't wait to try it ! 

I am hoping you can then import selection cells back off that sheet into MF pro but until I have looked I am not sure I will know how to reference an individual selection.

Thanks

Alfaman

OK Theodore,

I wonder if you can help me - I am a bit thick!

I am using Excel 2003 so have opened that file and followed the instructions above, but all I have at the moment is one slection info in cell K1,  like this:

ImportFromMFP(2,101506385,"Horse Racing - Todays Card / 13:45 Curr - 1m2f Grp3","Recharge",1.97,1.99)

How do I get that into the relevant separate cells on the spreadsheet?
Thanks
Alfaman

Hi alfaman,
in MF  the formula on custom cell K1 must have an equal sign in front:

=ImportFromMFP(market_index, market_id, "market_name", s_1_sel_id, "s_1_sel_name", s_1_back_price, s_1_lay_price)

As you started the market with market_index 2 in your MyMarkets list, you should see automatically in Excel (in row 2) from A2 to F2  your data.

Best Regards,

Theodore

That was quick, thanks - I will try again.  I think I did put an equal sign in and just got the value '-3' but will try again.  (I start 1st and second markets to see if anything changed - it did).


edit:  Oh yes its working now - time to play !   Thank you.

Theodore,

I will have a think as to how I can use this,  but for what I would like to do I think I need every selection from every market listed down the spreadsheet, one on each row.
 
I would then want the excel spreadsheet to put values against the selections and re-import those values for use in triggers - heaven !

From your knowledge of what you have acheived already do you think this might be possible ?

Thanks Alfaman

Hi Alfaman,
for multiple selections / market you must read Remarks

Remarks

1. If we want to export more that 1 selections / market we have to export them as additional function parameters. They will be displayed on the same excel row.

Example:

=ImportFromMFP(market_index, "s_1_sel_name", s_1_back_price, "s_2_sel_name", s_2_back_price)


So, many selections / market will occupy adjacent cells in Excel (s_2_..., s_3_..., s_k_...), as they all have the same market_index.

The opposite is actually under development (multiple column range from Excel to MF market selections).

Best Regards,

Theodore

OK, thanks, I did appreciate that was the case with this UDF - I was just wondering about future possibilities (and how far in the future they might be).

Alfaman

Hi Alfaman,
there are 2 other things.
1. import a multiple column range
2. Use Excel (and perhaps a web service) as an alert mechanism for MF to be always on

best Regards,

Theodore

I think I have just worked out how to use this horizontal layout to do what  I need.

As races have different numbers of runners what will happen if i request the first 25 selection names and there are only say 6 selections - will it cause a failure ?

thanks
Alfaman

Hi
Been playing around with this and have got it working on it's own but what I hoping to do was add it to my existing sheet.
Problem is when I add a new sheet it is sheet7. So I've change that in module1, copied over all the code and added MFPcell in the name manager.  Also changed the custom cell to sheet7.

The cell K1 says true and I can see the odds in that string but it's not putting the data in the columns.

Any Idea what might be wrong? The other option was to just move my old sheet into your one.

The other thing that baffles me is how would we go about placing bets with this.
At the moment I have several sheets that read the current market. When there is more than one market it cycles round them all but the data will always be in the same cell and sheet.
So linking a trigger is easy but how would I place bets when the data is in different cells?

Thanks

Hi Maddox,

there is also code in ThisWorkbook vba.
The code is in fact the Workbook_SheetCalculate event. Go VBA (alt-F11) and click on ThisWorkbook on the left (under Microsoft Excel objects).
You will see a Private Sub Workbook_SheetCalculate(ByVal Sh As Object) with code below. You have to copy this code as well to the new workbook. This is where the MF Data are copied to the Excel cells.

So as I understand you calculate your parameters in your 6 sheets today.
When it is to place a bet,  cell_mysheetX_Ak or cell_mysheetX_A(my_var) user variables read your data from your sheets and the same cell addresses and you act accordingly.

Now, the cells that hold the calculated parameters in your sheets, each time have to look for in a different row in excelsheet (according to the market_index in MyMarkets list).


Is that correct?

I see the point and I will think about it.






Hi Alfaman,

any number of selections will not be a problem. It will leave the empty cells with 0 values.

The function takes a variable number of input parameters  (you can output more/less data from every MFP market to one Excel sheet).

ImportFromMFP() takes at least 2 input parameters

• market_index is not optional and must be number
• any other argument

Best Regards,

Theodore

Hi,  Maddox,

Did you get it working because I just added some formulae to the Sheets2 and 3 and I could see the selections coming up in cell K1 in the formula bar on sheet 1 but not getting distributed to the sheet where it was previously.  I do have a lot of lookups - would that slow it down too much ?

I did get a macro interrupt window whilst adding my formula so perhaps I have done something I shouldn't.  I will try again.

Alfaman