I am trying to copy data from a specific location on a webpage into an excel spreadsheet. The webpage, somewhat frustratingly, uses red text color to indicate a negative number instead of a simple minus sign. I'm trying to get my macro to type "-" in the destination cell on the spreadsheet before pasting whenever the value copied is red/negative.
Since the data I'm trying to copy is a percentage, I thought I could just use the if_then_else: image condition to do this whenever the red "%" appears on the part of the page where the value I need is located.
I took a screenshot of the red percentage symbol using Preview and pasted that in the image box and filled out the "if" and "else" actions. The problem is that the macro always indicates the condition as being "false" (as in there is no red "%" found) and thus never types the "-" before pasting, unless I turn the fuzziness dial up to about 50%, in which case it always indicates that the condition is "true" whether the % is red or not and thus will type the "-" every time.
When I check the "Display" box next to the fuzz meter, little green, semi-transparent boxes containing a percentage number appear at seemingly random locations, usually nowhere near the coordinates I've entered as the location of the image.
some possible explanations:
the image is too small? In theory I could just zoom in on the webpage from which I am copying data, but this would defeat the purpose of the macro since there are other values at other locations on the page I need it to copy before and after the one in question.
there is too much blank space in the image? Again, the image is just a tiny red percentage symbol with a white or black background so perhaps when I turn up the fuzz dial it just detects any similar-sized portion of the screen that includes a similar amount of blank space?
I have the location coordinates wrong? I've trade entering both the image's coordinates relative to the top left of my computer screen, and the coordinates relative to the top left of the window that contains the data but nothing seems to work.
Can anyone help me figure this out? Thank you!
images:
the screen as it looks with both the webpage I'm copying from and the spreadsheet I'm pasting in
Hi and welcome, noticed there are some things missing in the macro:
It's not clear what happens after Excel is activated, you need to specify which cell or where you want the active cell to be
You'll need to add a second Find image, or Click and Move image
I'd instead use Find Image and save it's finding as a variable
Maybe overkill, but better way to recognize text in your case: You mention the source is a website, so, you could grab an specific cell and process it at text instead of image.
And a general tip: Increase pause to 1-2 seconds and see if it helps. KM needs some time between switching between apps or processing macro steps.
Sorry, I should have pointed out that I am only looking at the percentage symbol for the value nearest the top left corner directly under the word "All" and to to the right of the word "Net Profit"
-what happens after cell is activated hasn't been a problem because the same cell on every respective sheet is the one where the data gets copied so I just make sure they are already selected before I run the macro.
-Do you mean adding a second Find Image to the same action box with the same image in it? Wouldn't this only make it more likely to find the image in the wrong places?
-I did look at Find image on Screen but am confused about how to save
the found image as a variable and use that in combination with the If_Then_Else action.
playing with pause times has certainly been a factor with other parts of the macro but doesn't seem to be in this one since the problem just seems to be getting it to correctly detect the desired image, which can be tested even if the macro is not running
You don't provide us with your exact workflow, nor a macro, so I'm just guessing.
One approach is to:
Select and copy all of the text fields on the web page.
Paste directly into Excel, retaining the source formatting.
This should result in the "red" data being "red" in Excel
Run an AppleScript that will go through all cells in the selection, and set the value negative if it is "red" (or not black).
Here is the script to do #3:
AppleScript to Set Red Cells Negative
All cells must be selected before trigging this script
property ptyScriptName : "Set Excel Red Cells to Negative Value"
property ptyScriptVer : "1.1" -- ADD Test for Not Text
property ptyScriptDate : "2020-08-28"
property ptyScriptAuthor : "JMichaelTX"
set redRGB to {255, 0, 0}
tell application "Microsoft Excel"
set cellList to (cells of selection)
repeat with oCell in cellList
tell oCell
set curVal to (its value)
set curValType to class of curVal
if (curValType ≠ text) then
set cellColor to its font color index of font object
set cellColorRGB to its color of font object
(*
Two Methods:
1. Test for Color ≠ Black (color index 1)
2. Test for Color = Red
*)
-- 1. Test for Color ≠ Black (color index 1) --
--if ((cellColor ≠ 1) and (curVal > 0)) then
-- 2. Test for Color = Red --
if ((cellColorRGB = redRGB) and (curVal > 0)) then
set its value to -1.0 * curVal
end if
end if
end tell -- tell oCell
end repeat
end tell
While the web page may look "red", it may, or may not, be pure RGB Red.
So, in my script you can either test for the RGB color (which you can adjust), or test to see if it is NOT black. Use whatever works best for you.
Thank you for your response! here is the macro I'm referring to. I don't know how to run applescripts - also the non negative numbers on the webpage appear as more of a dark gray so I'm hoping to find a solution within keyboard maestro. Hope this helps! NP Fill.kmmacros (25.5 KB)
That's fine, if you can find a reliable KM solution.
IMO, trying to determine if each number is "red" using KM found image or color pixel is not likely to work well.
I'm having trouble downloading your macro, so I'll come back and edit this after I get it downloaded.
EDIT: OK, I've now looked at your macro.
Can you try this, manually:
Select all of the fields on the web page that are of interest.
Copy
Paste into a blank Excel Sheet
Save this file, zip it, and upload it here.
That will give us something we can work with to help solve your problem.
Thank you again for your replies! Here is that zip file. KMproject1.xlsx.zip (6.5 KB)
I hope this is what you meant. These are all of data that need to be *-1 that came up on a sample macro run, but depending on the situation, they could all be different and there could be more or fewer of them.
A bit more about the issue: the webpage is for studying trading tools for trading markets (foreign currency, metals, stocks etc. The down arrow key in the macro doesn't scroll through different fields on the webpage, it scrolls from one price chart (in this case currency pairs) to the next.
Since I'm recording data from 28 different pairs I have the macro set to repeat 28 times. A new webpage is loaded each repetition, but I'm copying the same fields each time (total net profit, total profit factor, total wins and total losses) and only the first of them needs to be *-1 if it's "red." There are thousands of tools to test, each with different settings and variables and stuff, and each adjustment alters which pairs have a positive or negative profit.
tl;dr: this zip may or may not be helpful because, on one hand, there is only one field of interest; on the other hand, there are virtually infinite ways to calculate the number in that field and I need to record as many of them as possible, as quickly as possible.
Basically, the image is very small and looks very similar to any other red text characters as far as image matching is concerned. If you can restrict your search to a small area, then Keyboard Maestro can better match it. You are doing that, but your area is in absolute coordinates, so I don't know where on the screen that might be - 457 from the left and 315 from the top of the main display, and then 100 x 100 rectangle below and to the right of that point.
Make sure it is searching in the correct place.
But even so, it is very hard to detect just text, because text tends to vary a lot, with anti-aliasing and subtle size changes.
Also, I note Keyboard Maestro is complaining about Translocation with the yellow warning triangle, and you should resolve that:
Thanks for the Excel file. That helped.
I'm working on a solution that works like this:
1. Select Cell in Excel that will be the Upper Left Corner of the Data to Paste from the Web page.
2. Select ALL cells on the web page, both black and red, that are of interest, that you want to copy.
3. Trigger this Macro
4. The maro/script will set all "red" numbers to a negative value.
Example Output
It should be ready to post later tonight, or tomorrow.
I took a completely different approach than the one you were using.
Instead of trying to ID the negative numbers by using Found Image, I copied the data to Excel, and then tested each cell there for the Red RGB color.
I think this is much more reliable, and probably faster as well.
Amazing work! Thank you so much for you help!
The macro successfully changes the "red" values negative! The only issue now is when I repeat the macro, it still stops and asks me to select a cell with the target color even though there are numbers in the magenta box. Here is how I modded the macro to repeat the required amount of times. Is there a way to make sure it "remembers" the rgb value so that the macro can run many times in succession, switching to the next page of the webpage/spreadsheet and selecting the cell of interest each time?
A couple things I'm noticing:
When I use execute your macro, if the value selected on the webpage is not "red", it copies the data into the selected spreadsheet, then a box prompting me to select a cell with the "RED" color before continuing pops up, and when I select a cell with that color test already inside it, a new error box pops up saying "MACRO CANCELED due to script Error", and "[ERROR] no Red Cells were found with a RGB of 221, 46, 2."
When the data selected on the webpage IS "red," it copies the data into the selected cell in the spreadsheet, switches it from positive to negative and then the error box pops up "MACRO CANCELED due to script Error", and "[ERROR] no Red Cells were found with a RGB of 221, 46, 2."
when I run the macro I made incorporating yours so that the actions repeat the needed number of times, it's a little different:
For one, when i follow the instructions of the error box prompting me to select a cell with the "red" color, the data selected on the webpage is pasted into that cell over the data that was already there IN ADDITION to the cell I selected before running the macro, though only the latter is switched to a negative number. Also, the macro typically only gets through a few repetitions before timing out and aborting.
You are not using my macro as I intended.
My macro expects you to select ALL cells on the web page, and then it will copy/update all of them.
It is NOT intended to work on one cell at a time.
Look at my video/animated GIFF. That is the process you need to follow.
After all of the cells are in Excel, then you can use that data however you wish.
You must follow this process:
1. Select Cell in Excel that will be the Upper Left Corner of the Data to Paste from the Web page.
2. Select ALL cells on the web page, both black and red, that are of interest, that you want to copy.
3. Trigger this Macro
4. The maro/script will set all "red" numbers to a negative value.
That is the process for the entire web page -- NOT just one cell.
If you are following this process and still having trouble, then please make a video of what you are doing and post a link to that.
My apologies! This is all quite new to me and I'm doing a poor job describing the issue and helping others help me.
First here is a video of my attempt to run your macro as intended. I am still not succeeding as usually it just copies and pastes the data without prompting me to select a cell with the target color and then shows the "No red cells found" error.
However even if it if it does work as intended, this doesn't quite solve my issue and again that is my fault for not outlining the criteria clearly or correctly enough. Here is a video of the macro I made but was trying to modify when I first decided to seek help here:
As you can see, my macro selects the fields of interest, copies their data and pastes it into the corresponding cells in the spreadsheet and then automatically switches to the next page (in Excel) and scrolls down to load the data for the next currrency pair (in the browser) and repeats.
The main objective of the macro is to allow me to record the data from all 28 pairs quickly. Every time I adjust the method of calculating those values, I have to run the macro to get the data again in the next column on the spreadsheet.
So if yours works as intended I still need to do all of the following to resolve my issue and get where I'm trying to go:
Delete the unwanted data that got copied (as I said earlier, there are only four specific fields on the page that I'm looking for and there are other fields between them so they cannot all be selected without roping in unwanted data).
change the red numbers that aren't net profit back to positive (again, I only need to make the "red" numbers in the "Net Profits" row switch to negative, and not the "red" numbers in the "Profit Factor" row).
transfer the data to my existing spreadsheet or build a new one around it.
repeat the macro and all of the above steps every time I scroll down to the next pair and the next pair and so on.
If this is the best way to use KM to turn those numbers negative, it would still be faster to just run my original macro and then go through the 28 pages manually to change the red numbers from positive to negative (unless if I can find a way to build a new macro to automate most or all of these tasks).
Again, I am sorry if I have communicated these needs poorly and I greatly appreciate your efforts
Your videos are way too small for me to read.
Please create videos at least HD size (1920 x 1080). It would be best to be an actual video with you providing verbal narration of what you are doing, and trying to do. You can publish to YouTube as a private video, and just share the link here.
Thank you! I can't seem to figure out how to add audio for verbal narration but here are links to the videos on Youtube and I will type what the narration would have been.
DEMO Macro 1 (my macro): https://youtu.be/o_c3kammQ-I
-The purpose of my macro is to quickly copy data from the tradingview website to my excel spreadsheet. Before triggering the macro I SELECT ALL SHEETS in the spreadsheet, click on the cell where I want the FIRST data point to be pasted, UNGROUP the sheets, and finally make sure the tradingview page is displaying data from the LAST of the 28 currency pairs I want to look at.
(0:02) when I trigger the macro, it first activates the website and hits the down arrow to scroll down to the next pair, since it was initially set to the final pair, it just goes back to the top to the FIRST pair I want to look at.
(0:03) the macro copies the data from four separate fields of interest and pastes them into their corresponding cell in the spreadsheet.
-(0:08) after the last of the four fields has been copied and pasted, the macro tells Excel to switch to the next page, where the first target cell is already selected, and then the macro starts over and uses the down arrow key to load the data for the next currency pair on the website.
(0:09-0:15) this video shows one more repetition of the macro, but I have it set to repeat 28 times in total so that it imports the data from all 28 currency pairs. When I adjust or change the tools used to calculate this data, I select the next column in the spreadsheet and begin the process again.
OBJECTIVE: I need to make a macro that completes this task but changes the NET PROFIT numbers (the third of the four fields being copied and pasted) from positive to negative in my spreadsheet IF they appear "red" on the website. The data from the other three fields being copied do not need to be changed, even if they are "red." At this time, I do not need to copy data from any fields other than the four I am already copying.
DEMO Macro 2 (your macro): https://youtu.be/o2aUq-_eFek
This macro is intended to (as I understand it) copy selected data from the website and paste it into a spreadsheet, prompt the user to select a cell containing the "red" color, and then change the data in ALL cells containing that color from positive to negative.
(0:01) I circle the magenta box in the macro to show that the variable is blank.
(0:04) I select the upper left-most cell in the spreadsheet as directed. The fields in the website are already selected.
-(0:07) the macro copies the data into the spreadsheet
-(0:10) an error box pops up with the words (MACRO CANCELLED due to script Error; [ERROR] No Red Cells were found with a RGB of 221, 46, 2).
I am not sure what I am doing to cause the macro to fail to prompt me to select a cell with the target color and change the data cells with that color from positive to negative. But even if I am able to make it do this, I am still just as far from achieving my objective as I was with my macro above, if not farther. As I said in the post above, even if this macro works as intended, it would appear that I still need to accomplish the following:
Delete the unwanted data that got copied (there are only four specific fields on the page that I'm looking for and there are other fields between them so they cannot all be selected without roping in unwanted data).
change the red numbers that aren't net profit back to positive (I only need to make the "red" numbers in the "Net Profits" row switch to negative, and not the "red" numbers in the "Profit Factor" row).
transfer the data to my existing spreadsheet or build a new one around it.
repeat the macro and all of the above steps every time I scroll down to the next pair and the next pair and so on.
If this is the best way to use KM to turn those numbers negative, it would still be faster to just run my original macro and then go through the 28 pages manually to change the red numbers from positive to negative (unless if I can find a way to build a new macro to automate most or all of these tasks).
I hope this is helpful in understanding my objectives and my current position.
Who would have thought two texans would have so much trouble communicating
Thank you for the demo vid w/ instructions! I've followed them but it still doesn't prompt me to select a cell with the "red" color. I figured out how to do video w/ audio so here is a demo with narration like you suggested. I start with your macro and then a partial demo of my existing macro to clarify what I'm trying to do.
To be clear, it will NOT ask you to select a cell if it finds cells with the "red" RGB that match the default set in the Macro.
Sorry, I misled you. The Video needs to be set for "unlisted" instead of "private". "Private" does not allow anyone but you to view the view.
So if you will just change the setting to "unlisted", and save, I should be able to view the video.
so I'm hoping to find a solution within keyboard maestro. Hope this helps!
