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

I've got it running again but have hit another snag.  Now I have all the selections layed out, one market per row and the selections name in each column and using a lookup assigned a value to each of these on sheet2, but how can I refer to these values in the triggers.

I thought I might be able to use the notation S[row:column] and use market_index:sel_index  to give a reference of where to look for the value on the spreadsheet.

But it appears that I have to refer to the column by its letter - can anyone think of a workaround so that I can convert sel_index to a column letter (eg  Sel_index 1 = B, sel_index 2 = C etc etc) to find the value assigned to that selection ?

Thanks
Alfaman

Hi Alfaman,

unfortunately the MF notation is S[column]:[row] and row is not a real row but only 1,2,3 (each selection occupies 3 rows on a querymarket sheet).

I am actually trying something else as a solution. I will move named range names with the excel rows as they display MFP data. So, when you will use these named range names in formulas in a second sheet, you will not have to worry from which row in sheet1 they come from. In this second sheet, your calculated cells will be in fixed addresses and triggers will be able to read them.

I will post it on Wednesday.


Hi Maddox,

the same solution will be applied to your case where your sheets read the current market.
I will post a concrete example later in the morning


Of course things should be solved if we could use the notation cell_Sheet1_F[column]:[row] where F in the place of S, column sel_market_index and row sel_index.

I posted something similar in the suggestions on June 5

http://community.marketfeederpro.com/suggestions-6/sel_market_index-market-selection-mfp-multiple-output-to-one-sheet-excel/


Best Regards,

Theodore

 

Theodore,

Do you get crashes working with MF pro ?  I opened a new spreadsheet last nigh whilst MF pro was linked to you spreadsheet and I got an "Abnormal Termination".

It does not happen everytime, but this has happened to me before when linking to Excel.

Alfaman

Hi,
perhaps a sheet1 existed in the spreadsheet you opened. I will rename it to something difficult to exist, when I will post on Wednesday

Best Regards,

Theodore

Hi all
Alfaman i'm more or less at the same stage as you. I got it working by moving my old sheet into this one. Just need to place bets now. You said about lookups slowing down excel. From what I found out using INDEX and MATCH formulas are much more efficient.

Theodore thanks I did do what you said but got a debug error in the Thisworksheet code

'flag that Workbook_SheetCalculate event happened
    Worksheets(MFPSheetName).Range("MFPcell").Value = -3

Sounds like you can see my problem. Great work and roll on Wednesday.

Thanks Maddox,

In really noddy terms, can you explain how you prompt your excel sheet to place bets.  I have seen the VAB code in the manual but what actually prompts this to happen at a precise moment ?

Sorry to appear thick but I have not got my head around this yet as you can tell.

With regard to finding the value appertaining to a seclection I did try nesting the cell_reference and including a table of column labels vertically down a blank sheet so that 'cell_sheet4_A(sel_index)' would return a letter corresponding to the column that the selection is in (to be used in the second reference) - it didn't work so I guess nested references are not allowed.

Role on Theodore's next great creation.

Thanks - on the shoulders of giants and all that .....................

I wouldn't have a clue how to use the vba to place bets. The way I do it atm is have a new market sheet open for every market. Then in customs cells I got a list of about 40 entrys. On a sheet I called "green" got a little area for all the values. So green!A1 will be s_1_back_price, green!B1 will be s_1_back_amount etc. Then you can link to those cells to do whatever. With the trigger you just enter what cell to read. Could be cell_green_A1 or anything really.

This only works if I got add new market sheet checked. That way it all updates at the same time. If I refresh more than 4 markets at once then I start to have delays.
It's slow but it works.

That any clearer?

Yes, thanks a lot for that - I always wondered.

By jove I think I’ve cracked it – for my needs anyway.

By putting all the selection names for each market into a row, then using lookups (or index and match) to marry the selection name to the data appertaining to that selection and putting it all into a massive column (say column C of Sheet4), allowing 30 rows per market (many of which will be blanks), we can then use that data in triggers by referring to it by:

cell_sheet4_C(market_index-1)*30+sel_index)

Thereby dropping down the spreadsheet by 30 rows for each market plus one row for each selection in the market in question.  So the 5th selection in the 3rd market will be on row 65 for example.

Not tested it yet but will do so as soon as time permits.

Alfaman

Hi Alfaman, it is clever.

What I am actually testing, will facilitate things more.
I will finish testing today and tomorrow I will post my proposal.

Best Regards,

Theodore

Why is it that sometimes when you "Launch Excel" it connects to the open spreadsheet and other times it opens a new blank workbook "Book2".

Anyone know ?

Hi Alfaman and Maddox,

Some minor changes have been done to make things independent from your sheets.
The Excel sheet that displays MF Data is now named "MFSheet".
All input data are moved in two subroutines
1. DefineGlobalParams()
2. DefineCellNames()

everything you might want to change in Excel is in the above subroutines

In MF the custom cell now is:
MFSheet!C1
and in Excel you see it on cell C1

All MF Data are displayed in MFSheet from row 2, as:
shiftrowdown = 1
row1 = shiftrowdown + 1

In Excel code exist in:

1. module1 (sub DefineGlobalParams(), sub DefineCellNames(), Funct ImportFromMFP())
2. ThisWorkbook Excel object (Workbook_SheetCalculate, Workbook_BeforeSave)

if you want to move code in another Workbook.


the solution is described below:

MF Data are output in rows according to market_index.
What I have done is to create (one cell) named ranges that redefine automatically their addresses every time, on the new row that displays MF Data.

So as an example let's suppose that the  ImportFromMFP() custom formula in MF outputs the following parameters in Excel:

=ImportFromMFP(market_index, market_id, "market_name", s_1_sel_id, "s_1_sel_name", s_1_back_price, s_1_lay_price, s_2_sel_id, "s_2_sel_name", s_2_back_price, s_2_lay_price, s_3_sel_id, "s_3_sel_name", s_3_back_price, s_3_lay_price)

In Excel I defined the following cell names in public sub DefineCellNames

xl_market_name
xl_s_1_sel_name
xl_s_1_back_price
xl_s_1_lay_price
xl_s_2_sel_name
xl_s_2_back_price
xl_s_2_lay_price
xl_s_3_sel_name
xl_s_3_back_price
xl_s_3_lay_price

You can see them on top left in the Excel Name Box and MFPCell name as well
These excel cell names redefine automatically their address each time a new row is displayed.
So in a second sheet i.e. Sheet1 putting the formula "=xl_s_1_sel_name" on cell A3, it will always display s_1_sel_name of the current market.
The same for  formula "=xl_s_2_sel_name" on cell A6, it will always display s_2_sel_name and for formula "=xl_s_3_sel_name" on cell A9, it will always display s_3_sel_name  of the current market

Your trigger in MF will always read cell_Sheet1_A(sel_index*3+1) as before.

Even if MF Data display on different rows, on Sheet 1 we refer by named range names

The only thing you have to do is to add your named range cell names in public sub DefineCellNames

These cell names are stored on the array NRngCellNames()
NRngCellNames(i, 1) stores the cell name and
NRngCellNames(i, 2) stores the column position. The row address is always the row1 that displays MF Data

Just uncomment the commented lines to add.
If you have more than the 20 cell names (in comment), continue with:
NRngCellNames(21, 1) = "xl_yourcellname": NRngCellNames(21, 2) = 32  'column position

You can add 200 cell names.

Save and Workbook_BeforeSave event will create your additional cell names

You cal also use excel name manager to delete my cell names (with xl_ prefix) and define your own with your own notation. Pay attention only to "MFPCell" do not delete it.

Sheet1 has all you need to define your own template to be read from MF. I followed the format of the querymarket sheet that has been made for the job.

PS.
If you omit in Public Function ImportFromMFP the line:
row1 = market_index + shiftrowdown

only the current market will be displayed in row1=2, and the template sheets will continue to do the job.

OnError please on the forum.

Best Regards,

Theodore

Cheers for the update, I'll have a go now. I think Oxa should put you on the payroll.

Alfaman are you using the latest version of mfp? It has an option to connect to an already open sheet or open a new one.

Theodore thanks for all your work - I have downloaded it and will have a try with it later when the markets finish.

Maddox - no, I found just too many crashes with the latest version so went back to 6.3 - I suppose I will have to be brave and try again but I found it a complete nightmare and that was the only change I had made.

Alfaman

Oh man - what am I doing wrong?

Again I can see the selections refreshing in C1 but not being put onto the correct rows.  It did row one which worked with one manual market refresh,  but then after I had saved the spreadsheet and actviated all the markets - nothing.

Alfaman