Script to desseminate broker statement into trade journaling software

[mechanicjon]

I've combined the worksheets with sample and descriptions into a workbook. I'm okay with making a simple spread sheet, anything more I'm a noobie.
I've been trying to figure this out on my own with no luck. To the point of pounding my head on the desk. That didn't help either.:banghead::banghead::banghead:
y help would be greatly appreciated.:thumb

Thank You
Jon

Windows10, Office Pro Plus 2013

[mechanicjon]

I need to build an array? I'm I headed the right direction?

[p45cal]

How to work out what goes into column J of the Corrected Template (Decimals)?
Also column K (Lots traded)?

[mechanicjon]

How to work out what goes into column J of the Corrected Template (Decimals)?
Also column K (Lots traded)?

Any combination of currency pairs that includes the "JPY" will be 2 or 3. (I see I made a mistake "J5" should be 2 ) All others will be 4 or 5. The price in column H or I will be the easiest place to pick up that value.
Column "K" Lots Traded uses the value in column "D" Volume, 1 lot =10,000
Also Column's L and M have to be manually edited.
column O swap fee = rollover

Thank you again
Jon

[p45cal]

Attached is a start. Get onto the Trade Log Sample sheet and run the macro blah4, then have a look at the Corrected Template sheet from row 9.
One difference the short trade's exit/entry prices. I'd have thought that the earlier price was the entry price and that's how I have arranged it, but…

[mechanicjon]

1. Great start! To understand the bought vs sold and short vs long. It gos back to how the trades are done by the market. You are correct by the earliest date first. BUT a short your first entry is a "SELL" & "BUY" is the last or close of the trade. Easy way to understand shorting or "short" position is example: I borrow your car and "sell" on Monday for $1000.00. I'm convinced on Wednesday I can "Buy"the same car for $750.00. I return your car and put the $250.00 in my pocket. If i have to "buy" the car for $1250.00 on Wednesday it cost me $250.00 and it was a losing trade. Sounds shady but it's legal.
2.Column H & I need to keep same number formating as original ( decimal place) Program uses that to determine if its a JPY pair.
3.The decimal column "I" gets its value from how many decimal pointsare in the values in H or I. H2 =0.95230 five numbers after decimal so J2 = 5.
4.I changed number formatting in column N to show 2 decimal points.
5.To determine long or short you can go by cell location in original log. If itwas sold first and bought later it was a short. If it was bought first and sold later it was a long.

thank you again

[p45cal]

1. I've no problem understanding shorting. It's more down to terminology. The following I'm used to and think are the same vis-a-vis timing:
Entering a trade
Opening a position
Selling short
Buying long

Likewise:
Exiting a trade
Closing a position
Covering a short
Selling my holding

Now you can enter a long position by buying stock
Also you can enter a short position by selling stock you don't own
Both the above entries are earlier than the later exits, viz.:
Selling my stock holding
Cover (buy back) my short position.

Would it be more appropriate to head the columns:
Buy Price and Sell Price?

Do I need to change where I put the numbers?

2. & 3. Just confirm that you get the report from your broker with the cells in columns E and F formatted to different numbers of decimals according to the type of trade. If so I can get that number directly.
4. Easy enough to do.
5. I think that's already done.

[mechanicjon]

Will renaming effect the import function of software? I wouldn't it would because its looking for column address not the name. Yes buy price and sell price fits better, but that's the template that came with software. the4xjournal.com

#2 & 3 the numbers come formatted correctly.

[p45cal]

OK. I understand what's going on now; the destination sheet is going to be used to import your broker's data into another piece of software.
We can put the the Entry/Exit Prices where you like (though I do think they should be the other way around) we can but try one way and see how it goes.
What I'd like to see is an un-tampered-with file from your broker so I can determine the best and most robust way to deal with it. (Perhaps they offer more than one type of report? -who's the broker?)
I can understand you not wanting this to be in the public domain, but if you're happy for just me to see it, Private Message me here for my email address where you can send it to me privately. It will answer questions I don't yet know I'll even want to ask!
If it's not an Excel file it probably doesn't matter.
And if there's a guide to filling in the spreadsheet, that'd be useful too.

[mechanicjon]

FXCM is my broker. I called them earlier and opened a free demo account and set some trades. Keeps my personal info private that way. The report is available anytime at users request. Just set date parameters for beginning and end,
select either html, pdf or excel file. The software webpage the4xjournal.com has video tutorials and a support page that is just the template download. A free trial program is available for up to 30 trades. If that helps at all.
I attached a report a the template. It's compared to my monthly reports, they can have 100-200 trade entries.

thank you
Jon

[p45cal]

As before, get onto the Trade Log Sample sheet and run the macro blah4 (or click the button on that sheet), then have a look at the Corrected Template sheet from row 9.
It now picks up the number of decimals from the way the cells containing the prices are formatted by FXCM and expressly formats a few other cells to have 2 decimals.

Notes.
I had a look at the video on importing data from FXCM. Check this yourself, 2 minutes in, it looks as if the entry/exit price data is being copied over as I have done it, based on time. Also note how he writes in Long/Short at 3mins:50secs into the video.
The macro code as it stands won't work directly on the FXCM report but I think it would be more robust if it were to; it could pick up data such as the date of the statement and automatically save the file with the right date as part of the file name.
I've also put a couple of questions to Steve of The4xJournal.com

ps. Actually, while writing this I've just been looking again at the data in the file attached, and I think there are 3 short trades followed by one long trade - I've not altered the code to accommodate this. So the attached I think needs a fair bit of correction but I'll wait to hear from you first.

[mechanicjon]

p45cal ROCKS! :thumbThank you for you help and patience. I know at times I frustrated you, do to my misunderstanding and lack of knowledge. Also a BIG thank you to everyone at VBA Express..:yes:yes