How to extract percentage from BM table

Help Studio
1

I have data in the following format. When I extract it to a text or Excel file, it gets stored in a single cell. I need to validate whether the returns are 100% based on the time period. If the returns are exactly 100%, I should mark it as ‘Pass’; otherwise, it should be ‘Fail’. I was able to extract the percentage after ‘Return USD’ using line-by-line reading with regex. However, I am unable to extract the data based on the time period. Could you please help me with this?

Before reading data

The image displays a table comparing various financial benchmarks across different time periods. (Captioned by AI)
The image displays a table comparing various financial benchmarks across different time periods. (Captioned by AI)770×400 8.2 KB

after reading and extract data to txt or excel

Time Period BMK
2025-02-05 - 2025-02-15 S&P 500 return USD 30%
Bloomberg zyd 500 return USD 25%
Blk MUCCSS return USD 30%
US smallCAP return USD 15%
2024-01-31 - 2025-02-15 S&P 500 return USD 10%
Bloomberg zyd 500 return USD 25%
Blk MUCCSS return USD 40%
JAPAN INDex return USD 25%
2020-12-31 - 2024-01-31 Bloomberg 500 return USD 3%
S&P 500 return USD 40%
MCCI Net index return USD 7%
UTC Gross return USD 50%

2

Hi @Raghu_km

Can you try generate datatable from text activity

Hope this helps

3

I appreciate your advice. But how is it going to work? I can’t extract the data into columns as it is stored in a single cell. How can we segregate all the cell data based on the time period? Can you create a workflow for this?

4

Hi @Raghu_km

Can you try the below

This image depicts a flowchart outlining steps to build a data table, process information with regular expressions, iterate through line items, and write the data to an Excel workbook in different ranges. (Captioned by AI)
This image depicts a flowchart outlining steps to build a data table, process information with regular expressions, iterate through line items, and write the data to an Excel workbook in different ranges. (Captioned by AI)1147×1169 61.8 KB

Output:

The image shows a spreadsheet with two columns, "Date" and "Percentage", listing three date ranges all with a percentage value of 100. (Captioned by AI)
The image shows a spreadsheet with two columns, "Date" and "Percentage", listing three date ranges all with a percentage value of 100. (Captioned by AI)512×367 3.75 KB

Sequence2.xaml (15.3 KB)

Regards,

1 Like
5

I sincerely appreciate your support. Your solution was very helpful!

Just a small correction—if the date is in the following format instead of numeric:

Jun 30 2024 - Jan 31 2025

What would be the correct regex for this format?

Thank you in advance!

6

@Raghu_km

Try this

((\w+(\s+|-)\d+(\s+|-)\d+ - \w+(\s+|-)\d+(\s+|-)\d+.*\s+)\w+.*\s+\w+.*\s+\w+.*)

The image displays a regex pattern, text, and matches, highlighting financial data entries with periods, returns, and indices in an online regex testing tool. (Captioned by AI)
The image displays a regex pattern, text, and matches, highlighting financial data entries with periods, returns, and indices in an online regex testing tool. (Captioned by AI)1427×757 59.2 KB

Regards,

7

I tried this its not working, where exactly i need to paste. please let me know.

8

@Raghu_km

Try this

Sequence2.xaml (15.3 KB)

Output:

A table in a spreadsheet lists dates and corresponding percentages, with three date ranges and associated percentages of 100. (Captioned by AI)
A table in a spreadsheet lists dates and corresponding percentages, with three date ranges and associated percentages of 100. (Captioned by AI)561×292 3.73 KB

Regards,

9

Thank you for your time. It is working if the data is the same. However, if the data varies, the total return giving 200% instead of 100 (Refer-1).
We have different types of accounts, and the data will vary. Some accounts may have only one entry (Refer-2).

Sometimes, the data can be extensive, containing more than 16 or 17 lines. Additionally, within the same period, you may see more than 5-6 indices contributing returns on same period(Refer-3)

Refer-1
benchmakdetails
Time Period BMK
Jun 30 2024 - Jan 31 2025 HDFC Bank return USD 100%
Jun 30 1958 - Jun 30 2025 ICC Bank return USD 100%

Refer-2
benchmakdetails
Time Period BMK
Jun 30 2024 - Jan 31 2025 HDFC Bank return USD 100%

Refer-3
benchmakdetails
Jan 31 2023 - Dec 31 2024 Axex 500 return USD 50%
N&P 500 return USD 40%
MCCC Net index return USD 5%
ITC Gross return USD 5%
Aug 31 2000 - Jan 31 2023 S&P 500 return USD 15%
Bloomberg zyd 500 return USD 5%
Blk MUCCSS return USD 10%
UR smallCAP return USD 10%
Blk MUCCSS return USD 10%
JAPAN INDex return USD 10%
Axex 500 return USD 10%
HDFV 500 return USD 10%
SBI 500 return USD 20%

10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.