unable to disable/delete embedded Web Query links in excel - screen shots added

Copper Contributor

I have created a worksheet using "web select" to input data from financial statements online. 

This is supposed to save me time and effort of manually typing in and proofreading thousands of lines of information. However, this has only caused me hours and hours of investigating and locating cells with links which are causing errors and strange behaviors/stealing valuable computer resources due to (my own uneducated label(s))  "data crunching" or ""unnecessary resource stealing".

 

I have been working with different techs on MS 365 support with no successful outcomes.

Everyone resorts to finally performing file and software repairs, which do NOTHING to fix the problem(s).

One tech even suggested I vacuum my computer to stop my computer from "spinning".

 

Is there anyone familiar with "web select / copy&paste" ? 

 

Downloading from the financial institution is not possible in any other format than PDF. The best method I have found is Web Select, but it has problems. Other methods? or a fix for the Web Select embedded code problem?

 

MS tech suggested using Word as a remedy. The screen shot showing "input via Web Select" was input by Web Select (column) - Copy - Paste to Word - (disregard the pasted picture/image - which contains the embedded code) - Copy text - Paste text into Col E of Excel spreadsheet beginning with cell E2.

 

Screen shots  - in reverse order, i tried to input opposite view, but it went in backwards

 

 

jchemis_4-1678587785385.png

 

jchemis_3-1678587607621.png

 

jchemis_2-1678587463058.png

 

jchemis_1-1678587408477.png

10 Replies

@jchemis 

 

I import financial data (personal, not work or business related) all the time, but I do it by first exporting the data from the financial institutions, all of which make that fairly easy to do. Some do it via Excel files, most (really, all) offer to export CSV files (which in at least one case--I'm looking at you, American Express--is far to be preferred to their abomination of an XLSX file).

 

So depending on what it would take to start over, instead of cleaning up what you already have, I'd recommend investigating that export-then-import route. 

@mathetes 

Thank you for your suggestion.

 

Generally, this should work as long as the financial institution offers CSV or QIF etc. 

Most of my financial institutions offer PDF only, especially if it is older than 12 or 18 months.

 

I have Acrobat software, but that doesn't consistently or accurately translate text 100% of the time.

 

I have tried highlight and paste, which only works to grab rows, and translates into a mess of columns that are not congruent and requires much cleanup.

 

Web Select by page/rows also results in a mess of incongruent columns.

Web Select by column is VERY accurate, and fairly simple to paste right into excel, if you can trust that it isn't also embedding hidden code in the excel file.

Web Select - first copy into Word - then copy and paste to Excel appears to not include hidden code, until copy and paste to Excel proves the formulas and conditional formatting do not work properly due to the hidden/embedded code (Web Query).

 

I need to figure how to locate and remove the embedded code which is sometimes (not always) generated by Web Select.

 

Remaining options include manually type and proofread thousands of lines of data, or manually copy and paste thousands line by line.

 

 

@jchemis 

 

Generally, this should work as long as the financial institution offers CSV or QIF etc. 

Most of my financial institutions offer PDF only, especially if it is older than 12 or 18 months.

 

Ouch. Yes. I'm working only with relatively current data (the oldest maybe a few months old).

 

Well, let's see if other folks have other ideas.

https://techcommunity.microsoft.com/t5/excel/cell-has-a-list-wrapped-in-html-split-list-into-2-separ...

Do you want to clean data after having downloaded datas from web like above case?
Thanks for your reply, I am not sure how to respond to your question because I doin't understand what you are asking. But I am NOT downloading - I am using Micorsoft's Edge tool "Web Select" via a copy&paste type of method.

Web Select - first copy into Word - then copy and paste to Excel appears to not include hidden code, until copy and paste to Excel proves the formulas and conditional formatting do not work properly due to the hidden/embedded code (Web Query).

do you want to fetch data directly from the website rather than "Web Select" via a copy (like below) & paste?

Screenshot_2023-03-12-10-54-03-236_com.microsoft.emmx.jpg

Hmmm. that sounds like it should simplify my life!
does that apply to my situation?

....I am trying to pull down YEARS of data from financial institutions which currently only offer PDF downloads for data older than 18 months (useless to me, since i need transactional data in excel format, CSV might work, or a reliable method of PDF to ANY format without EXTENSIVE rework to format properly)

 

I have no idea your case in detail.
if you want to parse PDF file,there are many api service available.

adobe pdf api
c# aspire
https://developer.adobe.com/document-services/apis/pdf-services/

and online pdf to excel
https://smallpdf.com/pdf-to-excel

pdf to text
http://e.anyoupin.cn/bsbm/stu60/a83/spire/upload_pdf.html

I cannot find a method to accurately parse data via the VERY expensive Acrobat PDF software (with CRAP support) I already own..... thank you for your input.

 

I'm not sure what is API, but looking into that...

 

not sure why I am investing so heavily in Acrobat, when I can't already "online pdf to excel", but looking into that.

 

I will try the other option (pdf to text) you offered...Maybe I am over-invested in Acrobat?

 

THANK YOU!

 

api is a interface or software package by which support program language (e.g c# php java) can automate your work.