If Formula with text and numbers

dilshod_k

New Member
Joined
Feb 13, 2018
Messages
39
Hi there,
I'm trying to write code for backtesting of trading strategy.
Given:
Active Worksheet with:
Range C2:C200 with range of Text values: Buy, Sell or empty cell
Range B2:B200 with Closing Price
Lets assume B2= 7.30 is a price per share


Reference Worksheet ("Control") with:
Range.B("10").Value= 1000 is a Buying power in USD
Range.B("11").Value=5 is commission per trade in USD

How to write formula with conditions as it follows:

if C2 = "Buy" Then (1000-5)/7.30 write down result in D2 - whole number!
if C2 = "Sell" Then (D2*7.30)-5 write down result in E2
if C2 = empty cell then do nothing

Thank you in advance
 

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
See if that does what you want.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$7.30</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Sell</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">992.8</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=IF(<font color="Blue">C2="Buy",ROUNDDOWN(<font color="Red">(<font color="Green">Control!B10-Control!B11</font>)/Sheet2!B2,0</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=IF(<font color="Blue">C2="Sell",(<font color="Red">ROUNDDOWN(<font color="Green">(<font color="Purple">Control!B10-Control!B11</font>)/B2,0</font>)</font>)*B2,""</font>)</td></tr></tbody></table></td></tr></table><br />
 

dilshod_k

New Member
Joined
Feb 13, 2018
Messages
39
See if that does what you want.

BCDE
2$7.30Sell992.8

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
D2=IF(C2="Buy",ROUNDDOWN((Control!B10-Control!B11)/Sheet2!B2,0),"")
E2=IF(C2="Sell",(ROUNDDOWN((Control!B10-Control!B11)/B2,0))*B2,"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
Thanks a lot for a prompt response.
I'm afraid I was not able to use this formula as I need to use it as VBA code.
Apparently I did not described properly the whole story.
I've got excel file with VBA code that allows download of historical prices for hundreds if not thousands of shares. For each share code creates separate sheet with name=symbol of the share. After that code makes calculations (for each/and on each sheet separately) based on several indicators and gives signals: Buy, Sell or empty cell.
I tried to write code But as I'm just doing first steps in VBA coding I can't write with proper syntax, so I use combination of Macros recording with manual adjustments.
Here is the sample of the code I tried to write:

Dim ws As Worksheet


For Each ws In Sheets

If ws.Name <> "Control" And ws.Name <> "Response" Then

ws.Activate


Range("Q1").Select
Selection.NumberFormat = "?#,##0.00"
ActiveCell.FormulaR1C1 = "EQUITY"
Range("Q2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-4].value = ""BUY"",RoundDown((Control!B10-Control!B11)/ActiveSheet!B2,0),""""))"
Range("Q2").Select
Selection.AutoFill Destination:=Range("Q2:Q150")
Range("R1").Select
Selection.NumberFormat = "?#,##0.00"
ActiveCell.FormulaR1C1 = "(USD)"
Range("R2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-4].value = ""SELL"",Roundown((ActiveSheet!D2*ActiveSheet!B2) - Control!B11,0),""""))"
Range("R2").Select
Selection.AutoFill Destination:=Range("R2:R150")

Range("R2").Select

End If
 

Forum statistics

Threads
1,082,099
Messages
5,363,129
Members
400,720
Latest member
Pettel

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top