Multiple task at once

ride2esc

New Member
Joined
Jun 13, 2015
Messages
12
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
ABCD
1Bot, [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
2Bot, [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
5DateMarket (bittrex, binance, etc)Profit/lossOverall profit/loss
603.02.21 20:48Bittrex1.56.19
703.02.21 21:05Binance0.413.85
Sheet729
Cell Formulas
RangeFormula
A6:A7A6=IFERROR(MID(REPLACE(A1,FIND("]",A1),255,""),FIND("[",A1)+1,99),"")
B6:B7B6=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(A1,CHAR(10),REPT(" ",99),2),99))," ",REPT(" ",99)),99))
C6:C7C6=IFERROR(LEFT(SUBSTITUTE(TRIM(MID(A1,SEARCH(C$5,A1)+26,255))," ",REPT(" ",99)),99)+0,"")
D6:D7D6=IFERROR(LEFT(SUBSTITUTE(TRIM(MID(A1,SEARCH(D$5,A1)+20,255))," ",REPT(" ",99)),99)+0,"")
 

ride2esc

New Member
Joined
Jun 13, 2015
Messages
12
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.
 

Attachments

  • telegram paste.png
    telegram paste.png
    43.3 KB · Views: 10

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Hi
If VBA's Ok with you Try
VBA Code:
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

New Member
Joined
Jun 13, 2015
Messages
12

ADVERTISEMENT

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

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Alt+F11.
Insert Module
Past the code
The sheet with your data should be active
Run
 

ride2esc

New Member
Joined
Jun 13, 2015
Messages
12

ADVERTISEMENT

See my attachment error.
What I am doing wrong?
 

Attachments

  • error.png
    error.png
    114.3 KB · Views: 6

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Try this version
VBA Code:
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
 
Last edited:

ride2esc

New Member
Joined
Jun 13, 2015
Messages
12
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
Try it like
VBA Code:
    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
 

Forum statistics

Threads
1,141,069
Messages
5,704,111
Members
421,327
Latest member
Msh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top