Excel question

• 09/02/2017 at 2:18 PM #45226
  Despair

  I have an excel-related question and I must say to start with that I have no real clue about Excel. 🙁

  I have a column of prices all with a dollar sign at the end. Now I want to get rid of the dollar sign and just have the numbers. Is there an easy way of doing that?

  

  Despair

  Blocked

  Topics: 97

  Replies: 1034

  Been thanked: 219 times
  09/02/2017 at 3:56 PM #45229
  Andre Vorster

  Most of the reports exported have some currency symbol with it. This is just text and the column is not formatted as currency.

  To get rid of these (false) currency symbols:

  Highlight the area of your report or column.
  Press Ctrl+F (hold in control button while press F)
  This will open the find function – select the TAB on top [Replace]
  In the [Find what] field – type in $ sign (or any other character your want removed from your column/area)
  In the [Replace with] field – leave blank to remove the $ sign  (or any other character you typed in find field above)
  Click – [Replace all]

  This will remove the false $ sign. The same process goes for any other symbol.

  Now if you want to format the column with a real $ sign
  Select the column or area
  R/Click – Format cells – Select currency – OK.

  Here are few key strokes to create currency symbols (hold in the Alt and type in the number)

  Alt 156 £ Pound
  Alt 0128 € Euro
  Alt 36 $ Dollar Sign
  Alt 155 ¢ Cent
  Alt 157 ¥ Yen
  Alt 159 ƒ Frank / Gulder

  Hope it helps. Please ask if you have any other queries (or have frustrations with Excell 🙂

  I cant code but I will help with any other contribution to this site.

   

  1 user thanked author for this post.

  Despair

  

  Andre Vorster

  Participant

  Topics: 25

  Replies: 117

  Been thanked: 48 times
  09/02/2017 at 4:07 PM #45230
  Andre Vorster

  To format the column or area as numbers:

  Highlight the area or column – R/Click – Format cells – Number – Select the format you want your numbers to be displayed as
  The same can be done to show column/area as currency
  Highlight the area or column – R/Click – Format cells – Currency  – Select the format you want your currency to be displayed

  Now you can do normal calculations on these columns/areas.

  Watch out for the , (comma) that’s hiding between the numbers..!
  Some reports have a “,” (comma) sign used as 1000s separator.
  These cells wont calculate.!!!
  Follow the same process to remove the (comma) sign as what you did with the $ sign above.

  

  Andre Vorster

  Participant

  Topics: 25

  Replies: 117

  Been thanked: 48 times
  09/02/2017 at 4:16 PM #45231
  Despair

  Thank you so much Andre.  This was very detailed. I hope I can fix it now. Otherwise I will bother you again. 🙂

  

  Despair

  Blocked

  Topics: 97

  Replies: 1034

  Been thanked: 219 times
  09/02/2017 at 4:27 PM #45232
  Andre Vorster

  Anytime!
  Pleasure. Glad I can help.

  The same frustration you might have with Excel the same I have with coding.
  This way we help and support each other here.

  2 users thanked author for this post.

  Nicolas, Despair

  

  Andre Vorster

  Participant

  Topics: 25

  Replies: 117

  Been thanked: 48 times
  09/02/2017 at 8:04 PM #45254
  Despair

  I have one more question for you Andre. If I now want to multiply every cell with a constant (for example to convert dollar to euro). How can I do that. So i want to have a new column with every cell multiplied by x.

  

  Despair

  Blocked

  Topics: 97

  Replies: 1034

  Been thanked: 219 times
  09/02/2017 at 9:50 PM #45255
  Andre Vorster

  Yes what you want is possible. I have a sheet that converts $ and Euro to £.
  I think the best is if you can send me the part of the excel sheet that you want the formulas in. In the sheet plese make some notes/comments what you want where.

  That will give me clear understanding and you will get what you want. Then I’ll fill in the formulas and paste it back here again.

  Do you work with IG reports? I already have some reports but it might not be what you need/want.

  

  Andre Vorster

  Participant

  Topics: 25

  Replies: 117

  Been thanked: 48 times
  09/02/2017 at 10:40 PM #45259
  Andre Vorster

  Something like this?

  

  Andre Vorster

  Participant

  Topics: 25

  Replies: 117

  Been thanked: 48 times
  09/02/2017 at 10:43 PM #45260
  Andre Vorster

  Att

  Wont allow me to upload Excel file…

  

  Andre Vorster

  Participant

  Topics: 25

  Replies: 117

  Been thanked: 48 times
  09/02/2017 at 11:50 PM #45263
  Despair

  I just drag´n drop the results of a backtest from PRT to excel. Then I want just the column with the p/l of each trade extracted and from this column I want to take away the currency sign (you told me how, works fine) and convert als , to . This is how far I got.

  But now I would need this results converted to USD and some strategies give results in JPY or something other.

  

  Despair

  Blocked

  Topics: 97

  Replies: 1034

  Been thanked: 219 times
  09/03/2017 at 2:17 AM #45264
  Andre Vorster

  Let’ see if I can upload xls sheet this time. Previous there was no response and the sheet did not upload.

  Sorry the xls sheet does not want to upload. The best I can do now is to make screenshot of it.
  Please ask if you dont understand especially since you cant work/calc with a image.

  

  Andre Vorster

  Participant

  Topics: 25

  Replies: 117

  Been thanked: 48 times
  09/03/2017 at 2:24 AM #45266
  Andre Vorster

  Nicolas.

  Why cant I upload Excel spreadheet? I can upload image. Guess it has to do with security and anti-virus etc.

   

   

  

  Andre Vorster

  Participant

  Topics: 25

  Replies: 117

  Been thanked: 48 times
  09/03/2017 at 9:37 AM #45273
  Despair

  Think I haven’t made clear what I ant. I know how to multiply 2 cells in Excel for example. I just need help to multiply a whole row (every single trade). I want in the result to have every single trade in USD and not only the final sum.

  

  Despair

  Blocked

  Topics: 97

  Replies: 1034

  Been thanked: 219 times
  09/03/2017 at 4:19 PM #45297
  AsgerK

  Don’t know if this is what you mean. But you multiply the two cells, in my case (B4*E2) but then you put in dollarsigns like this (B4*$E$4) (can be done by pressing f4 if you are on windows), then you click on the cell, then you click and hold on the small square in the bottom right corner of the cell and drag it down. Then you mark it all and copy it into new cells. Then this sign appears (pic 2) and you click on the sign the arrow points at, and you get the values.

  1 user thanked author for this post.

  Despair

  

  AsgerK

  Participant

  Topics: 1

  Replies: 6

  Been thanked: 3 times
  09/04/2017 at 8:24 AM #45330
  AVT

  @Despair

  I have a MM Table (that’s LibreOffice, former OpenOffice which is Exel conform but free and import to Exel should be possible). From that you can see how to do calculation of all sorts. Yellow is what you enter manually, blue what is autocalculated. Link: https://www.dropbox.com/s/r0ai3clmoi6nwi8/MM_1EurCfd-ab-2017.ods?dl=0

  Hope you find all kind of stuff you need in there.

  1 user thanked author for this post.

  Despair

  

  AVT

  Participant

  Topics: 17

  Replies: 186

  Been thanked: 58 times
• Author
  Posts