Author Topic: Formula Advice  (Read 5528 times)

Tags:
  • All members
  • Posts: 231
  • Gender: Male
Formula Advice
« on: 19 Oct 2017, 23:23 »
Hi Oxa,

        

I bet through an Excel spreadsheet, and I have a query on applying a formula in the conditions of a trigger.


My spreadsheet is set up through a trigger which uses the "Write to Excel" command and it looks like this:


     Col A                               Col B             Col C          Col D     Col E     Col F

Horse Name                Horse Name             Price         Rank1    Rank2     Bet Amt

"Royal Symphony"      Royal Symphony        $2.50           2              1       $5.00

"Harmony"                   Harmony                 $5.00           1              2       $2.50


Full list for the race follows down the columns in consecutive rows.


[I need to show the horse name without "" (Column B) as I keep records in another sheet which only recognises the name without the inverted commas.]


The trigger I am trying to write places dutch bets on horses where rank1 and rank2 are equal to 1 (the top ranked horse).


In the trigger conditions, I think I need to use a VLOOKUP to identify the ranks by horse.


For example, AND Selections Trigger Expression VLOOKUP("sel_name",SheetName!$A$2:$E$500,4,FALSE) is equal to 1

                     OR    Selections Trigger Expression VLOOKUP("sel_name",SheetName!$A$2:$E$500,5,FALSE) is equal to 1


BUT I am unsure of the MFP convention for writing a formula like this into a trigger.


My queries are:


Do I put an "=" sign before the VLOOKUP function?

Do I need to have sel_name in inverted commas?

Do I need to use Column A (name in inverted commas) or Column B (no inverted commas) in the VLOOKUP expression?

Is a VLOOKUP function valid in a trigger condition?
Is there a simpler way of doing this?


In addition, and for any trigger I might need in the future along the same lines, what is the convention for a VLOOKUP in the amount field? Is a VLOOKUP even valid in that field?

For example, can I refer to a VLOOKUP in the amount field as follows:

VLOOKUP(sel_name, horse_sheet!A2:E500,6,FALSE).

Same queries as above still apply.

Could you advise the correct formula conventions please.


Your assistance is much appreciated.


Regards,


bobh

  • Administrator
  • Posts: 8821
  • Gender: Female
*
Re: Formula Advice
« Reply #1 on: 25 Oct 2017, 14:01 »
Hello!

Unfortunately, the Excel function VLOOKUP is not supported in MF Pro. The list of functions supported can be found in the manual, chapter "Triggered Betting - Reference - Operators and Functions". You cannot just call an Excel function or a function of any other external software from inside MF Pro.

What you can do though is use a plain text file instead of Excel. Is there any way you can save the data in the spreadsheet in a text file, with the values in each row separated by a tab character (\t)? That way you could import these values into the program via "Import selections for auto-trading" and then address the rank of each horse by imported_4.
Always try your triggers in Test Mode before switching to real money!

Follow us on Twitter.

Join our WhatsApp chat!

Присоединяйтесь к официальному Telegram-каналу!

  • All members
  • Posts: 231
  • Gender: Male
Re: Formula Advice
« Reply #2 on: 26 Oct 2017, 01:31 »
Thanks Oxa,
Unfortunately your suggestion will not work with my trigger, as many calculations are made on a separate worksheet to arrive at Rank1 and Rank2, and one of the calculations is market price at 30 secs before the start.

The reason for my request was that I was trying to avoid creating a worksheet for each race through the Excel interface, as Australian races on a Saturday can sometimes total over 100, and that makes the entire workbook very cumbersome. I am currently using that procedure to make my bets, but writing the prices into each worksheet can be slow, and occasionally it misses a race.

Thanks for your assistance once again,

bobh


  • Administrator
  • Posts: 8821
  • Gender: Female
*
Re: Formula Advice
« Reply #3 on: 26 Oct 2017, 07:04 »
I see and I totally understand your trying to avoid creating a separate sheet for each market. But if you are going to use Excel functions, that's probably the only way out.

You can tick off "Add layout to spreadsheets", "Display current bets in Excel" and delay the creation of market sheets until the very moment they are needed. Maybe also experiment with the refresh rate.
Always try your triggers in Test Mode before switching to real money!

Follow us on Twitter.

Join our WhatsApp chat!

Присоединяйтесь к официальному Telegram-каналу!

 

Please note, BetFair is seems to be currently OFFLINE