Pages:
Actions
  • #46 by Maddox on 09 Jul 2010
  • 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
  • #47 by tpanos on 11 Jul 2010
  • 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.
  • #48 by Maddox on 11 Jul 2010
  • Thanks I've taken that line out and seems to be running well.

    Cheers
  • #49 by alfaman on 20 Jul 2010
  • 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
  • #50 by tpanos on 20 Jul 2010
  • 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


  • #51 by alfaman on 21 Jul 2010
  • 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
  • #52 by alfaman on 22 Jul 2010
  • 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
  • #53 by tpanos on 22 Jul 2010
  • 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


  • #54 by tpanos on 22 Jul 2010
  • Quote from: alfaman on 22 Jul 2010, 07:40
    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
  • #55 by alfaman on 22 Jul 2010
  • Theodore,

    Thanks very much I will try what you say.

    Alfaman
  • #56 by Maddox on 22 Jul 2010
  • Thanks for the update, done that line about the memory leak and it's running great.
  • #57 by alfaman on 23 Jul 2010
  • Yes  - mine is up and running as well.

    Thanks
  • #58 by Maddox on 23 Jul 2010
  • 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
  • #59 by tpanos on 23 Jul 2010
  • 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
  • #60 by Maddox on 23 Jul 2010
  • You are a legend, that's fixed it thanks :)
Pages:
Actions