Author Topic: Please help me with this excel  (Read 1545 times)

Tags:
  • All members
  • Posts: 314
Please help me with this excel
« on: 19 Sep 2019, 21:59 »
Hi,

I just cannot figure out how to do this, the documentation is not giving me the answer, I am seeing loads of references to sheet1!A1, which can be used in a trigger, and yet, when I try it, I just cannot get it to work.

Please see the attached images.

I am obviously doing something wrong, but there is no possible way to debug through this, and so I have no way to figure this out myself


Thanks for the help

I have never in all my years of development been at a loss on how to debug through anything, except when I use MFP - I wish you would put debug facilities inside MFP, then I would never have to pester anybody! There is no coding language that I cannot figure out myself, and yet, the major limitations of MFP are stopping me right there in my tracks - very frustrating. BUT, you never seem to take any notice of me, so I will continue to criticise it....


  • Moderator
  • Posts: 3597
*
Re: Please help me with this excel
« Reply #1 on: 20 Sep 2019, 07:07 »
Hi
to reference data in a connected Excel sheet cell use the variable cell_ 

for example:
selections back price is less than cell_shtStakes_A1 
or selections trigger expression cell_shtStakes_A1 is greater than back_price

or to bet the amount in the cell on say second fav:
amount: cell_shtStakes_A1
condition: selections rank is equal to 2
 
Please read the following disclaimer with regards to the information you may request and obtain on our forum. This specifically concerns trigger files and various instructions as to how to implement a strategy.

  • All members
  • Posts: 314
Re: Please help me with this excel
« Reply #2 on: 22 Sep 2019, 12:02 »
MarkV,

Thanks so much for your help.

I suppose the response to my question could have been RTFM properly! I would say in my defense, I was not aware of the 'Excel Bound Variables' and my reference to the manual, revealed on page 102, {sheet_name}!{cell_address}...

Now that I am able to GET values from cells within the excl sheet, I am now trying, unsuccessfully, to SET values in cells in excel.

I will ask the question here, but if it is not the place to ask, I will ask a fresh question in another post...

given that I can get values using cell_sheet_cellReference, I assume I cannot write values to excel using that syntax?

I have a plan to write the selections' values, at settle time, on the horses. I want to write ["market_name"] in column A, ["sel_name"] in column B, and [sel_place] in column C. I am referring to COLUMN, rather than A1, B1, C1, because I want to write a value for each selection, in its own row, for each market that is used.

I decided to have a formula in excel to give the row number of the next empty cell, and I used =SUMPRODUCT(MAX(($A:$A<>"")*(ROW(A:A)))) + 1

I have this formula on cell J1

The formula shown will give the last filled cell in column A, and I put in +1, to give me the next row, which has an empty cell, in column A...

So, with that, in order to write these 3 entries into the excel sheet, I wanted to use a triple set of write to Excel actions as :

write ["sel_name"] Cell address shtSheet!B(cell_shtSheet_J1)
write [sel_place] Cell address shtSheet!C(cell_shtSheet_J1)
write ["market_name"] Cell address shtSheet!A(cell_shtSheet_J1)

so, if the last empty cell in column A is A5, then the first action above will write the selections name into B5, it win status (win or lose, 0 or 1), into C5, and finally the market name into A5..

Once these 3 values have been added, the formula shown above will detect an entry into A5, and the value in J will now be 6...

Seems logical enough, but I am not sure this will work...

Why?

Well, in order to test the theory of referencing the value in J1, I did a straight user variable check, as cell_shtSheet1_J1, and it gave me the value 5, no problem

BUT

by manually adding a value into cell B5, such as 'hello' - When I did cell_shtSheet_B(cell_shtSheet_J1), the value shown was the literal, as cell_shtSheet_B5

So, to check this I put another variable in there, as cell_shtSheet_B5, and the value shown was 'hello'

So, using a direct reference to cell_shtSheet_B5, I am able to see the value held in that sell, but using a construct as cell_shtSheet_B(cell_shtSheet_J1), Iam not able to see the value...

Did I do something wrong here?

Can my triple set of write action's work? Is there a different way I need to do it?

Thanks

Mark.

  • All members
  • Posts: 314
Re: Please help me with this excel
« Reply #3 on: 22 Sep 2019, 13:23 »
And just for completeness.. 

  • Moderator
  • Posts: 3597
*
Re: Please help me with this excel
« Reply #4 on: 23 Sep 2019, 06:36 »
Hi
Try this:

Constant: counter Value: 1
Constant: sheet_name Value: shtSheet

Trigger:
write to Excel "market_name" cell address: sheet_name!A(counter)
write to Excel "sel_name" cell address: sheet_name!B(counter)
write to Excel sel_place cell address: sheet_name!C(counter)
set user variable name: counter value: counter+1

so you set the constant counter value to the row you want to start writing to
Please read the following disclaimer with regards to the information you may request and obtain on our forum. This specifically concerns trigger files and various instructions as to how to implement a strategy.

  • All members
  • Posts: 314
Re: Please help me with this excel
« Reply #5 on: 23 Sep 2019, 11:09 »
Hi MarkV,

Thanks again for your help - I am pretty sure this should work.

Problem is, when I came to use it, I then get an OLE error message, shown through the trigger logs....

See image. Is there a fix for this, or do I need to update something on the PC/OS?

Thanks

  • All members
  • Posts: 314
Re: Please help me with this excel
« Reply #6 on: 23 Sep 2019, 11:12 »
just for completeness....

  • All members
  • Posts: 314
Re: Please help me with this excel
« Reply #7 on: 23 Sep 2019, 11:29 »
oh dear, you may have noticed my attempt to reference a non existent variable - T20D01, where it should have been T2ID01..

Yes, I have corrected this, but please feel free to abuse me for this...

Yes, it worked. MarkV, you are are the best, thank you so much!

I hang my head in shame!

BUT, I am secretly very happy because I have completed what I needed to do...


 

Please note, BetFair is seems to be currently OFFLINE