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?
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.
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.
Thank you so much Andre. This was very detailed. I hope I can fix it now. Otherwise I will bother you again. 🙂
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.
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.
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.
Att
Wont allow me to upload Excel file…
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.
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.
Nicolas.
Why cant I upload Excel spreadheet? I can upload image. Guess it has to do with security and anti-virus etc.
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.
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.
AVTParticipant
Senior
@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.
