Multiple task at once

[ride2esc]

Hello everyone
I am trying to make multi-calculation at once in excel, and firs I want to know if possible, and if yes..then how

From another tool I get some data which look like this:

Bot, [03.02.21 20:48]
Sell order executed at Bittrex
43.14492517 ZRX @ 0.73 USDT
Total: 31.49 USDT
Fees paid: 0.06 USDT
Average rate: 0.70 USDT
Break Even Price: 0.70 USDT
Profit/Loss of this trade: 1.50 USDT (5.28%)
Overall Profit/Loss: 6.19 USDT

Bot, [03.02.21 21:05]
Sell order executed at Binance
15634 HOT @ 0.00 USDT
Total: 12.99 USDT
Fees paid: 0.01 USDT
Average rate: 0.00 USDT
Break Even Price: 0.00 USDT
Profit/Loss of this trade: 0.41 USDT (3.38%)
Overall Profit/Loss: 3.85 USDT

And so on..
I want to extract in another sheet or in another column the following data:

Date	Market (bittrex, binance, etc)	Profit/loss	Overall profit/loss
03.02.21 20:48	Bittrex	1.50	3.85

After this is done, I want to do some calculations. Sums, and other, but those I know how.
So, I can copy and paste from the other program the data, then paste it in excel then the report.

Please advise.
Thank you

 

[jtakw]

Hi,

You didn't really give any detailed information about your Data/Text String...
Is each "set" all in 1 cell?
If so, does it contain carriage returns?
You only showed 2 samples, are All your data in the Exact same format?

Assuming all Yes to the above, try these:

Book3.xlsx
	A	B	C	D
1	Bot, [03.02.21 20:48] Sell order executed at Bittrex 43.14492517 ZRX @ 0.73 3USDT Total: 31.49 4USDT Fees paid: 0.06 5USDT Average rate: 0.70 6USDT Break Even Price: 0.70 7USDT Profit/Loss of this trade: 1.50 8USDT (5.28%) Overall Profit/Loss: 6.19 9USDT
2	Bot, [03.02.21 21:05] Sell order executed at Binance 15634 HOT @ 0.00 USDT Total: 12.99 USDT Fees paid: 0.01 USDT Average rate: 0.00 USDT Break Even Price: 0.00 USDT Profit/Loss of this trade: 0.41 USDT (3.38%) Overall Profit/Loss: 3.85 USDT
3
4
5	Date	Market (bittrex, binance, etc)	Profit/loss	Overall profit/loss
6	03.02.21 20:48	Bittrex	1.5	6.19
7	03.02.21 21:05	Binance	0.41	3.85
Sheet729

Cell Formulas
Range	Formula
A6:A7	A6	=IFERROR(MID(REPLACE(A1,FIND("]",A1),255,""),FIND("[",A1)+1,99),"")
B6:B7	B6	=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(A1,CHAR(10),REPT(" ",99),2),99))," ",REPT(" ",99)),99))
C6:C7	C6	=IFERROR(LEFT(SUBSTITUTE(TRIM(MID(A1,SEARCH(C$5,A1)+26,255))," ",REPT(" ",99)),99)+0,"")
D6:D7	D6	=IFERROR(LEFT(SUBSTITUTE(TRIM(MID(A1,SEARCH(D$5,A1)+20,255))," ",REPT(" ",99)),99)+0,"")

 

[ride2esc]

Thank you for your answer.
I think my example is bad
I am pasting the info from a telegram channel.

Each raw has it's own cell actually.

I can manually delete and paste new data for calculations.
Can this be done?
In your example the A column is taken, and I can not paste.
I have looots of data to paste in my clipboard, but as I said, each cell has it's own value.
lile in my prinscreen attached.
If excel can not do it, maybe you have other workaround/ideas to colect data from Telegram bot and to calculate that.
Thank you in advance for your time.

 

[mohadin]

Hi
If VBA's Ok with you Try

Sub test()
    Dim a As Variant
    Dim ar As Range
    For Each ar In Columns("A:A").SpecialCells(xlCellTypeConstants, 2).Areas
        a = ar
        a(1, 1) = Split(Split(a(1, 1), "[")(1), "]")(0)
        a(2, 1) = Split(a(2, 1), "at")(1)
        a(3, 1) = Trim(Split(Split(Split(a(8, 1), ":")(1), " ")(0), "USDT")(0)) * 1
        a(4, 1) = Trim(Split(Split(Split(a(9, 1), ":")(1), " ")(0), "USDT")(0)) * 1
        Cells(Cells(Rows.Count, 3).End(xlUp).Row + 1, 3).Resize(, 4) = Application.Transpose(a)
    Next
End Sub

 

[ride2esc]

Hey
For me anything that works is ok.
This is advance mode.
Please..tell me step by step how to use this VBA code.

What I did:
Alt+F11. Then pasted this code in Sheet1. Then run.
Then error - compule error invalid outside procedure.
Please advise

 

[mohadin]

Alt+F11.
Insert Module
Past the code
The sheet with your data should be active
Run

 

[ride2esc]

See my attachment error.
What I am doing wrong?

 

[mohadin]

Try this version

Sub test()
    Dim a As Variant
    Dim ar As Range
   Range(Cells(1, 3), Cells(1, 7)) = Array("Date", "Market (bittrex, binance, etc)", "Profit/loss", "Overall profit/loss")
    For Each ar In Columns("A:A").SpecialCells(xlCellTypeConstants, 2).Areas
        a = ar
        a(1, 1) = Split(Split(a(1, 1), "[")(1), "]")(0)
        a(2, 1) = Split(a(2, 1), "at")(1)
        a(3, 1) = Split(Split(Split(a(8, 1), ":")(1), " ")(0), "USDT")(0) * 1
        a(4, 1) = Split(Split(Split(a(9, 1), ":")(1), " ")(0), "USDT")(0) * 1
        Cells(Cells(Rows.Count, 3).End(xlUp).Row + 1, 3).Resize(, 4) = Application.Transpose(a)
    Next
End Sub

 

[ride2esc]

Same error message...
If I press debug - this line get's highlighted in reallow

a(3, 1) = Split(Split(Split(a(8, 1), ":")(1), " ")(0), "USDT")(0) * 1

 

[Fluff]

Try it like

    For Each ar In Columns("A:A").SpecialCells(xlCellTypeConstants, 2).Areas
        a = ar
        a(1, 1) = Split(Split(a(1, 1), "[")(1), "]")(0)
        a(2, 1) = Split(a(2, 1), "at")(1)
        a(3, 1) = Split(Split(a(8, 1), ": ")(1), "USDT")(0) * 1
        a(4, 1) = Split(Split(a(9, 1), ": ")(1), " ")(0) * 1
        Cells(Cells(Rows.Count, 3).End(xlUp).Row + 1, 3).Resize(, 4) = Application.Transpose(a)
    Next