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

Thanks
Excel memory doesn't increase, just mfp.
Done a test of running your sheet as per your instructions and my new super sheet with a long list of customs cells, also added all the extra code in vb

Code: [Select]

=ImportFromMFP(market_index, "market_parent", market_parent_id, "market_name", market_id, "s_1_sel_name", s_1_sel_id, s_1_back_price3, s_1_back_amount3, s_1_back_price2, s_1_back_amount2, s_1_back_price,s_1_back_amount, s_1_volume, s_1_last_traded, s_1_profit_loss, s_1_lay_price, s_1_lay_amount, s_1_lay_price2, s_1_lay_amount2, s_1_lay_price3, s_1_lay_amount3,"s_2_sel_name", s_2_sel_id, s_2_back_price3, s_2_back_amount3, s_2_back_price2, s_2_back_amount2, s_2_back_price,s_2_back_amount, s_2_volume, s_2_last_traded, s_2_profit_loss, s_2_lay_price, s_2_lay_amount, s_2_lay_price2, s_2_lay_amount2, s_2_lay_price3, s_2_lay_amount3, s_1_bm_backp, s_1_bm_backa, s_1_bm_layp, s_1_bm_laya, s_1_back_matched, s_1_lay_matched, s_1_bu_backp, s_1_bu_backa, s_1_bu_layp, s_1_bu_laya, s_1_back_unmatched, s_1_lay_unmatched,  s_2_bm_backp, s_2_bm_backa, s_2_bm_layp, s_2_bm_laya, s_2_back_matched, s_2_lay_matched, s_2_bu_backp, s_2_bu_backa, s_2_bu_layp, s_2_bu_laya, s_2_back_unmatched, s_2_lay_unmatched, stake, marg)The new sheet does eat up memory quicker. Good to hear about those patches, took me ages to define new cells.

Still like this sheet because I can monitor so many more markets at once and apart from the memory leak the performance is great.

Cheers

Hi Maddox,
I think the problem derives from the fact that you do not delete markets in MyMarkets list after they are finished. It is a problem of MF.

You can export from MF via ImportFromMFP only the current market if you wish (so you could delete the finished markets in MF).
This will reduce definitely memory consumption in MF.

It can be done.

Go to MFPtoExcel2007 file in function ImportFromMFP

'if you ommit the following line
'all rows will be superimposed in line 2
'it will be displayed in fact only the current market in row 2
row1 = market_index + shiftrowdown

All cell names will work as before.
I think this will not affect the execution of your sheets.

Thanks I've taken that line out and seems to be running well.

Cheers

Hi,

I am still playing around with this when time permits (version 1) and I (using Excel 2003) and I can get it running once, then if I modify the sheet to do something I want, I cannot get it running again other than to update the value in the MFP cell - not distribute it to rows.

There is something in Excel which thinks it knows better and is stopping the auto run marco from operating. 

I have come across something with Excel,when pasting txt (log) files into Excel when you want everything in one column, it does it once then the next time, having used the text to columns function in the meantime, it thinks that is what it should do and you have to reset it to be able to paste into a column.

Can anyone think of what I can do to keep the macro (Workbook_SheetCalculate) operating at all times - or what I am inadvertantly doing to stop it ?

Thanks

Hi Alfaman,

I think the problem has to do with worksheet and cell naming.
I am actually testing a new excel file that facilitates what had to be done manually before (concerning workbook - worksheet naming and named cell names, when we copy from other workbooks etc).

I will post it on the forum Wednesday, it will be better to cooperate on that excel file.

Best Regards,

Theodore

Theodore,

Many thanks - I am really looking forward to it.  You could not believe how much work this will save me and the improved accuracy I will get from the triggers if I can get this working reliably.

I am now using the latest version of MFP (6.3.2.2 I believe) as the trusty one I had used successfully for 3 months dissapeared repeatedly yesterday - so I thought "if it's going to be unreliable I may as well be so with the latest version".

Alfaman

Theodore,

Having upgraded to MFP 6.3.2.2 it all seems to do what I want.  Howerver, using your original version (which is perfect for mey needs) and having added stuff on the second and third worksheets it has been running smoothly with 55 markets refreshing, but when I save the spreadsheet and then re-open it , I still get that subscript out of range error.

Any thoughts?  I have not re-named anything to do with Sheet1 or MFPCell etc.

Thanks
Alfaman

Hi,
a new update is created that simplifies a lot all work.

All we have to do now is:

• write in MF custom cell and formula as we did before
• copy - paste (manually) the formula from MF to excel cell F1 in MFPtoExcel file (without the equal sign [=] in front of the formula
• Ctrl-S in Excel file
• launch excel from MF

That's all !!!

We launch excel from MF and we work.

We do not have to define cell names in sub DefineCellNames(), or excel file name or worksheet file names
Of course in sub DefineglobalParams() we can still customize

All documentation is in pages (1) and (2) of the post.
A new consolidated doc will be published next days as soon as I finalize the inverse (automatic, refreshable, multi column range, imported selections and vars for trading from excel to MF)

Example

Custom cell in MF

MFSheet!C1

Formula

=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)

(You write your own)

Do not forget double quotes ["] in string user variables

Copy paste formula in Excel file MFPtoExcel2003_V3.xls or MFPtoExcel2007_V3.xlsm

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)

(without the equal sign [=])

Ctrl-S Save in Excel

Launch excel from MF

Notices

• this update generates automatically cell names. all cell names are created with a standard prefix ("xl_". so the MF var s_1_lay_price will generate cell name xl_s_1_lay_price etc.
• you can change to your own prefix (in sub DefineGlobalParams() change CellNamePrefix = "xl_"
• there is no conflict if you copy your own sheets in MFPtoExcel (well I think so).
  you can rename the file without conflict.
• documentation in page (2) of the post remains active - except DefineCellNames() that  generates cell names automatically

I am waiting for your feedback.

Best Regards,

Theodore

Theodore,

Having upgraded to MFP 6.3.2.2 it all seems to do what I want.  Howerver, using your original version (which is perfect for mey needs) and having added stuff on the second and third worksheets it has been running smoothly with 55 markets refreshing, but when I save the spreadsheet and then re-open it , I still get that subscript out of range error.

Any thoughts?  I have not re-named anything to do with Sheet1 or MFPCell etc.

alfaman,

I propose you to copy your sheets at the new excel I uploaded.
There is strong possibility that your problem will be solved.
Also, other advantages exist (excel infos might display only current market, so, you will be able to delete the finished and settled markets from your MyMarkets list - memory issue  - Maddox had a similar scenario, read previous posts).
If not, delete all cell infos in your sheets (not sheets themshelves and send me the file to check it.

Best Regards,

Theodore

Theodore,

Thanks very much I will try what you say.

Alfaman

Thanks for the update, done that line about the memory leak and it's running great.

Yes  - mine is up and running as well.

Thanks

Having a problem adding my own list of values. When I add that list I posted in an earlier post the first cell col A is "xl_market_index" when it should be "xl_market_parent" and all the rest of the cell defs are offset to the right by one cell.

Could this be because of the order of values? which is different from yours.

ImportFromMFP(market_index, "market_parent", market_parent_id, "market_name", market_id, "s_1_sel_name", s_1_sel_id etc etc

Any ideas?
Thanks

You are right Maddox.

It is a bug. I will fix it.

To bypass it today, act as following:

In Excel cell F1 delete the first input parameter of ImportFromMFP market_index and all will be shifted in the correct place.

That means, when you cut and paste to cell F1 your function:

ImportFromMFP(market_index, "market_parent", market_parent_id, "market_name",............ stake, marg)

delete market_index.

 The new cell F1 will be:

ImportFromMFP("market_parent", market_parent_id, "market_name",............ stake, marg)

(In VBA function ImportFromMFP market_index is parsed and ignored. I forgot to do the some when I parse cell F1)


PS.
Hi Alfaman,  you must do the same at your ImportFromMFP() formula

Best Regards,

Theodore

You are a legend, that's fixed it thanks